CODEWITHSUNDEEP
mysqljoininnodbsql-deletememory-table
Aliweb
Aliweb

Reputation: 1951

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?

Upvotes: 0

Views: 229

Answers (1)

Barmar
Barmar

Reputation: 780974

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

Upvotes: 1

Related Questions