CODEWITHSUNDEEP
mysql
domino
domino

Reputation: 7345

Better way to write this MySQL query (JOIN)

DELETE FROM table1
WHERE field1=(SELECT id FROM table2 WHERE type=1) 
OR field2=(SELECT id FROM table2 WHERE type=1)

What's the proper way to write this query? It currently doesn't work because the subqueries return more than one row.

Upvotes: 1

Views: 52

Answers (3)

Nim
Nim

Reputation: 631

Depending on the size of your tables, you might be able to get away with the IN-approach. if they're on the larger side, you can go for the DELETE...USING syntax. 

DELETE FROM foo USING foo, bar WHERE foo.field1=bar.id OR foo.field2=bar.id

Upvotes: 1

Tchoupi
Tchoupi

Reputation: 14681

Use IN:

DELETE FROM table1
WHERE field1 IN (SELECT id FROM table2 WHERE type=1) 
OR field2 IN(SELECT id FROM table2 WHERE type=1)

Upvotes: 5

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

You can do this with IN:

delete
from table1
where field1 in (
        select id
        from table2
        where type = 1
        )
    or field2 in (
        select id
        from table2
        where type = 1
        )

Upvotes: 3

Related Questions