MYSQL: How to DELETE with INNER JOIN


MYSQL: How to DELETE with INNER JOIN

There are 2 tables, proxy and proxy2, and I need to delete data from proxy. proxy.md5 = proxy2.md5 is the only thing that “connect” the tables. I have tried this script but it doesn’t work:

DELETE FROM proxy x1 INNER JOIN proxy2 x2 ON x1.id >285078 AND x1.md5 = x2.md5

Result:
#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 proxy2 x2 on x1.id>285078 and x1.md5 = x2.md5' at line 1

The right method:

delete x1 from proxy x1 inner join proxy2 x2 on x1.id>285078 and x1.md5 = x2.md5

Delete successfully:

You can delete multiple tables:

delete guarantee_history,guarantee,customer,agent 
                from guarantee_history 
                inner join guarantee where guarantee.id = guarantee_history.guarantee
                inner join customer where customer.id = guarantee.customer
                inner join agent where agent.id = customer.agent
                where agent.trademark = 1

Leave a Reply