mysqljoininnodbsql-deletememory-table

MySQL delete from Memory table join InnoDB table

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?

Solution

  • 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).