busboy — 2011-07-03T11:51:43-04:00 — #1
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
gar_onn — 2011-07-03T14:25:10-04:00 — #2
select the ID of the row and then use 'DELETE ....... WHERE id =....'
busboy — 2011-07-03T14:28:29-04:00 — #3
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.
gar_onn — 2011-07-03T14:38:51-04:00 — #4
DELETE FROM ... WHERE id IN (select t1.id LEFT JOIN .... ON .... WHERE ....)
something like that should work
r937 — 2011-07-03T16:00:41-04:00 — #5
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
busboy — 2011-07-03T16:03:53-04:00 — #6
spacephoenix — 2011-07-03T16:39:17-04:00 — #7
Thread moved to MySQL forum
busboy — 2011-07-03T17:22:28-04:00 — #8
Ok r937, I'm ready for your assistance.
r937 — 2011-07-03T18:24:14-04:00 — #9
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
busboy — 2011-07-04T22:44:53-04:00 — #10
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.
r937 — 2011-07-05T15:50:32-04:00 — #11
ON searches.author = users.uID
AND searches.clicks < 4
WHERE users.last < CURRENT_DATE - INTERVAL 1825 DAY
busboy — 2011-07-05T18:17:32-04:00 — #12
r937, excellent and thank you again.