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.
FOREIGN KEYs
and a "cascading delete".