MySQL delete from Memory table join InnoDB table

Question

My query is:

DELETE FROM abc_memory INNER JOIN abc USING (abc_id) WHERE x < y

and MySQL complains by saying:

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 abc USING (abc_id) WHERE x < y' at line 1

In MySQL there's a limitation on neither joining a memory table with an innodb table nor deleting while joining two normal tables. But is there a limitation on deleting from a memory table while joining that with an InnoDB table?

Answer 1

When you use a JOIN in DELETE, you have to list the table names in the DELETE clause, to tell it which table(s) to delete from.

DELETE abc_memory
FROM abc_memory
JOIN abc USING (abc_id)
WHERE x < y

This will just delete from the abc_memory table. If you want to delete from both tables, change it to DELETE abc_memory, abc.

This is explained in the manual:

Multiple-table syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

You either have to list the tables after DELETE or in a USING clause (this isn't the same as the USING option in the JOIN clause).