CODEWITHSUNDEEP
mysql
davidkomer
davidkomer

Reputation: 3088

Delete all records from one table where they match criteria from two (or more) tables

Consider the following:

QUERY

SELECT * FROM 
`table1`,`table2` 
WHERE `table1`.`RemoteID` = `table2`.`ID` 
AND `table2`.`UserID`=1

How can I change it from a SELECT to DELETE from table1 where these records match? It must only delete from table1, not table2

In less specific terms, I want to delete all records from table1 where they match some criteria of both tables (discretely and relatively)

Upvotes: 0

Views: 61

Answers (2)

Akshat Bhargava
Akshat Bhargava

Reputation: 441

Try this,

Delete from table1 where Id in (select table1.Id from table1 t1, table2 t2 where t1.RemoteID = t2.ID AND table2.UserID = 1)

Upvotes: 0

Manwal
Manwal

Reputation: 23836

You can use IN with sub query

DELETE FROM table1
WHERE `table1`.`RemoteID` IN (
    SELECT ID 
    FROM table2
    WHERE `table2`.`UserID`=1)

Upvotes: 1

Related Questions