Multi Table Delete Error in MySQL

Today I was trying to write some SQL scripts to delete data from MYSQL database. And it uses INNER JOIN to join two tables for the delete. My query looked like follows.

DELETE fpsdb.future_changes.* FROM fpsdb.future_changes fc INNER JOIN fpsdb.income a ON a.Income_ID = fc.Dependancy_ID WHERE a.client_ID = 1;

When I try to execute, it gave the error "Unknown table 'future_changes' in MULTI DELETE"

I tried every thing to find the error without any success. Then I google for this error. Then I found out about an MySQL bug related to this issue. It states that "Cross-database deletes are supported for multiple-table deletes, but in this case, you
must refer to the tables without using aliases."

So I changed the query removing aliases and put full qualified table names everywhere.

DELETE fpsdb.future_changes.* FROM fpsdb.future_changes INNER JOIN fpsdb.income ON fpsdb.income.Income_ID = fpsdb.future_changes.Dependancy_ID WHERE fpsdb.income.client_ID = 1;

And Then it worked..... :)

 

Reader Comments

Really helpful. thanks a lot !

Thank you so much, you save me a couple of hours of work



Blog Archive