Jump to content

delete & select


Destramic

Recommended Posts

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

Link to comment
Share on other sites

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)
  • Like 1
Link to comment
Share on other sites

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 |
+----+---------------------+--------+--------+----------+
Link to comment
Share on other sites

@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 :suicide:

 

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

 

thanks guys

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.