CODEWITHSUNDEEP
mysqlsql
Michael Samuel
Michael Samuel

Reputation: 3920

Inner join and delete not working in mysql

I have two tables table1 and table2. I want to delete from table1 based on a condition in table2.

I have the following mysql query:

DELETE FROM table1 
INNER JOIN table2 ON table2.col1 = table1.col1
WHERE table2.col2 = '1'

This return a syntax error. Is there something wrong with the above syntax?

Upvotes: 1

Views: 64

Answers (3)

peterpeterson
peterpeterson

Reputation: 1325

You could do something like:

DELETE FROM table1 WHERE col1 IN (select col1 from table2 WHERE table2.col2 = '1');

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You need to specify the table you are deleting from:

DELETE table1
    FROM table1 INNER JOIN
         table2
         USING (col1) 
    WHERE table2.col2 = '1';

Upvotes: 2

Joe Taras
Joe Taras

Reputation: 15379

Try this:

DELETE FROM table1
WHERE EXISTS(
    SELECT 'C'
    FROM table2
    WHERE table2.col1 = table1.col1
    AND table2.col2 = '1'
)

Upvotes: 1

Related Questions