What is the best way to delete when a join is involved?
FROM users u
JOIN searches s
ON u.uID = s.author
(DATE_SUB(CURDATE(),INTERVAL 1825 DAY) > u.last) and clicks < 4
ORDER BY last
select the ID of the row and then use ‘DELETE … WHERE id =…’
My join query produces hundreds of rows right now. I am seeking a solution that will allow me to delete them all at once. Thanks though.
DELETE FROM … WHERE id IN (select t1.id LEFT JOIN … ON … WHERE …)
something like that should work
the SQL to do deletions using a join actually varies a great deal from one database system to another
here at sitepoint, we have a databases forum and a mysql forum, so since you did not post in the mysql forum, could you tell us which database system you’re running
Thread moved to MySQL forum
Ok r937, I’m ready for your assistance.
could you start by explaining which rows from which table you want to delete
you’ve got users and searches
but you never said which one you want to delete
Sorry about that. I’m wanting to delete from the searches table. I’m using the users table to see how long its been since they last logged in.
ON searches.author = users.uID
AND searches.clicks < 4
WHERE users.last < CURRENT_DATE - INTERVAL 1825 DAY
r937, excellent and thank you again.