Destramic Posted June 3, 2015 Share Posted June 3, 2015 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 3, 2015 Share Posted June 3, 2015 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) 1 Quote Link to comment Share on other sites More sharing options...
requinix Posted June 3, 2015 Share Posted June 3, 2015 You can't SELECT from a table that you're DELETEing from. Not even in a subquery. Fortunately Psycho's suggestion is better anyways. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 4, 2015 Share Posted June 4, 2015 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 | +----+---------------------+--------+--------+----------+ Quote Link to comment Share on other sites More sharing options...
Destramic Posted June 4, 2015 Author Share Posted June 4, 2015 @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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.