CODEWITHSUNDEEP
mysql
labu77
labu77

Reputation: 797

DELETE SQL with two tables

This SELECT Query works well:

SELECT u.user_id, u.user_lastvisit, n.user_id, n.multiple_counter, n.notification_id
FROM 
users u, notifications n
WHERE 
u.user_id = n.user_id
AND 
u.user_lastvisit < ( UNIX_TIMESTAMP( ) -1814400 )

I have to delete every entry in the notification table. I tried this:

DELETE notification_id FROM notifications
(SELECT u.user_id, u.user_lastvisit, n.user_id, n.multiple_counter, n.notification_id
FROM 
users u, notifications n
WHERE 
u.user_id = n.user_id
AND 
u.user_lastvisit < ( UNIX_TIMESTAMP( ) -1814400 ) )

Thank you for help!

Upvotes: 1

Views: 33

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

Your syntax of the DELETE statement is wrong. Use the following solution instead:

DELETE FROM notifications 
WHERE notification_id IN (
    SELECT x.notification_id
    FROM (
        SELECT notifications.notification_id
        FROM users, notifications
        WHERE users.user_id = notifications.user_id
    ) AS x
    WHERE x.user_lastvisit < (UNIX_TIMESTAMP() - 1814400)
);

You can use a INNER JOIN on DELETE for a smaller DELETE statement:

DELETE FROM notifications
    INNER JOIN users ON notifications.user_id = users.user_id 
WHERE users.user_lastvisit < (UNIX_TIMESTAMP() - 1814400)

Upvotes: 2

Related Questions