MYSQL - Delete Query with Join

Question

I have checked the answered questions. But, solutions are not working for me.

DELETE FROM TEST2
INNER JOIN TEST1 on TEST1.FIELD2 = TEST2.FIELD2
WHERE TEST1.FIELD1 = 22;

When i execute this query, i am getting the following error in phpmyadmin.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'INNER JOIN TEST1 on TEST1.FIELD2 = TEST2.FIELD2' at line 2

I am getting tired. I need help. Thanks in advance.

sgeddes · Accepted Answer · 2013-02-22 14:28:52Z

30

This should work:

DELETE T
FROM TEST2 T
INNER JOIN TEST1 on TEST1.FIELD2 = T.FIELD2
WHERE TEST1.FIELD1 = 22;

Sample Fiddle Demo

I think you can also do it with IN:

DELETE FROM Test2
WHERE Field2 IN (
    SELECT Field2 
    FROM Test1
    WHERE Field1 = 22)

answered Feb 22, 2013 at 14:28

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sgeddes Over a year ago

@user2003356 -- np, glad I could help!

Jeffery ThaGintoki Over a year ago

better to use the first one as it won't take much time to execute, instead the second one as it is correct, the first thing will happen is that the in section will be executed first and store a result then executing the primary query.

Collectives™ on Stack Overflow

MYSQL - Delete Query with Join

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related