Deleting rows when using a join

Databases
1

What is the best way to delete when a join is involved?

Thanks!

SELECT
u.uID,
s.searchDate,
u.last,
s.keyword,
s.alerts,
s.lastAlert,
s.clicks,
s.lastClick,
s.timeStamp
FROM users u
INNER
JOIN searches s
ON u.uID = s.author
WHERE
(DATE_SUB(CURDATE(),INTERVAL 1825 DAY) > u.last) and clicks < 4
ORDER BY last
limit 1000;

2

select the ID of the row and then use ‘DELETE … WHERE id =…’

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.

4

DELETE FROM … WHERE id IN (select t1.id LEFT JOIN … ON … WHERE …)

something like that should work

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

6

mySQL

7

Thread moved to MySQL forum

8

Ok r937, I’m ready for your assistance.

:slight_smile:

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

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.

Thanks!

11 
DELETE searches
  FROM users
INNER
  JOIN searches
    ON searches.author = users.uID 
   AND searches.clicks < 4
 WHERE users.last < CURRENT_DATE - INTERVAL 1825 DAY
12

r937, excellent and thank you again.