MySQL | You can't specify target table 'a' for update in FROM clause

Question

DELETE FROM table_a WHERE id IN(
    SELECT table_a.id AS id FROM table_a, table_b 
    WHERE table_a.object_id = 1 AND table_a.code = 'code' 
        AND table_a.code = table_b.code 
        AND table_b.id = table_a.b_id 
        AND table_b.table = 'testTable')

This is a (somewhat simplified) query I want MySQL to execute. I read on other pages of stackoverflow that this wasn't supported and that it's solvable by using JOINS. How could this be 'transcribed' to a query using JOINS? I find it hard to do so, because I've never tried creating DELETE queries with more than one table.

Answer 1

You can't delete from a table and reference the same table in a subquery — just a limitation of MySQL. Something like the following should work:

DELETE FROM table_a 
USING table_a
INNER JOIN table_b
    ON table_a.code = table_b.code
    AND table_b.id = table_a.b_id
    AND table_b.table = 'testTable'
WHERE table_a.object_id = 1 
    AND table_a.code = 'code' 

The important part is USING. If you just join the two tables, you'll delete records from both. USING tells MySQL to use these tables for processing, but only delete from the tables in the FROM clause.

http://dev.mysql.com/doc/refman/5.0/en/delete.html