delete & select

[Destramic]

hey guys i'm trying to delete a row from my database...but at the same time i want to select lifetime and modified column to inject into the query also.

 

i've tried many variations from online but I'm having no joy

DELETE FROM sessions AS s
WHERE IN (SELECT session_id FROM sessions s2 WHERE s2.session_id = s.session_id
AND UTC_TIMESTAMP() > DATE_ADD(s2.modified, INTERVAL s2.lifetime SECONDS))

can anyone help please?

 

thank you

[Psycho]

I'm not really understanding your question. But, your query appears to be invalid

[cod]WHERE IN (SELECT session_id . . . [/code]

 

WHERE "WHAT" is IN the select query.

 

Again, I really don't understand all the facets of what you are trying to accomplish, but I don't see that you need the IN() clause at all. Why not just this?

 

   DELETE FROM sessions
    WHERE session_id = s.session_id
      AND UTC_TIMESTAMP() > DATE_ADD(modified, INTERVAL lifetime SECONDS)

[requinix]

You can't SELECT from a table that you're DELETEing from. Not even in a subquery. Fortunately Psycho's suggestion is better anyways.

[Barand]

Not sure from your post what you are trying to achieve so this example may or may not be relevant.

 

Task: Remove old comments for items but only if there is a more recent comment, so we don't completely remove all comments for an item.

mysql> SELECT * FROM comments;
+----+---------------------+--------+--------+----------+
| id | dateAdded           | itemid | userid | comment  |
+----+---------------------+--------+--------+----------+
|  1 | 2014-11-01 00:00:00 |      1 |     12 | aaaaaaaa |  <- remove
|  2 | 2014-12-12 00:00:00 |      2 |     15 | bbbbbbbb |
|  3 | 2015-01-02 00:00:00 |      1 |     25 | cccccccc |
|  4 | 2015-03-25 00:00:00 |      3 |     44 | dddddddd |
+----+---------------------+--------+--------+----------+

DELETE a
FROM comments a
INNER JOIN comments b
    ON a.itemid = b.itemid AND b.dateAdded >= '2015-01-01'
WHERE a.dateAdded < '2015-01-01';

mysql> SELECT * FROM comments;
+----+---------------------+--------+--------+----------+
| id | dateAdded           | itemid | userid | comment  |
+----+---------------------+--------+--------+----------+
|  2 | 2014-12-12 00:00:00 |      2 |     15 | bbbbbbbb |
|  3 | 2015-01-02 00:00:00 |      1 |     25 | cccccccc |
|  4 | 2015-03-25 00:00:00 |      3 |     44 | dddddddd |
+----+---------------------+--------+--------+----------+

[Destramic]

@psycho hit it on the head

DELETE FROM sessions WHERE UTC_TIMESTAMP() > DATE_ADD(modified, INTERVAL lifetime SECOND)

was so simple...dont even know why i had asked the question...been on this computer everyday after work trying to work on my site for the last year...think i need a couple of weeks off

 

@barand i'm gonna save that query for the future...i'm sure it'll come in handy somewhere along the line

 

thanks guys