Mysql – Delete multiple rows in one MySQL statement

deleteMySQL

I have been looking at a lot of solutions, but I simply do not get it.
What I want to do is remove 2 rows, each in a different table:

DELETE FROM adv_equip, adv_players 
WHERE session = '9746qbcinll2edfbmkfn316lu0'

There is a session field in both tables.

A lot of examples include t1, t2 and whatnot, and I do not know how to use it. I tried for example:

DELETE FROM adv_equip t1, adv_players t2 
USING t1, t2 
WHERE t1.session = t2.session 
AND t1.session = '9746qbcinll2edfbmkfn316lu0'

What should I do? It's been days and I kind find a logical easy solution anywhere.

Best Answer

The multi-table delete syntax goes like this:

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

or like this:

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

Source: MySQL documentation.

The documentation also points out in the Multi-Table Deletes section:

Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement.

That is what is wrong with your second example in particular. Instead of

DELETE FROM adv_equip t1, adv_players t2
USING t1, t2
...

it should be

DELETE FROM t1, t2
USING adv_equip t1, adv_players t2
...

Ypercubeᵀᴹ has raised a few good points in the comments. Since one of the conditions in your WHERE clause is effectively a joining condition, you might want to express the join more explicitly in your statement:

DELETE FROM
  t1, t2
USING
  adv_equip AS t1 INNER JOIN adv_players AS t2
  ON t1.session = t2.session
WHERE
  t1.session = '9746qbcinll2edfbmkfn316lu0'
;

A join, however, comes more naturally as part of a FROM clause, so you might find the first variation of MySQL's multi-table DELETE extension more consistent with an equivalent SELECT. This is how it would look in your case:

DELETE
  t1, t2
FROM
  adv_equip AS t1 INNER JOIN adv_players AS t2
  ON t1.session = t2.session
WHERE
  t1.session = '9746qbcinll2edfbmkfn316lu0'
;

I would probably also prefer specifying .* after the target names in the DELETE clause with that syntax:

DELETE
  t1.*, t2.*
...

That, in my opinion, would more clearly be conveying the intention: delete rows rather than the tables themselves.

And last, but not least, point from the comments is that if only one of the tables has the matching rows, your DELETE statement will fail to delete any rows at all. That is very unlike two separate statements each deleting from one table at a time and making sure that in the end neither has the specified rows. Depending on the intended outcome, therefore, you might prefer two DELETE statements to one.

Related Question