Proper syntax for deleting from multiple tables in MySQL/PHP

Question

Each of the below lines work individually but I would like to combine them into one query.

$query4="DELETE FROM a WHERE email = '$email'";
$query2="DELETE FROM b WHERE email = '$email'";
$query3="DELETE FROM c WHERE email = '$email'";
$query1="DELETE FROM d WHERE email = '$email'";
$result = mysqli_query($con, $query[1-4, depending]) or die(mysqli_error($con));

I have tried a number of different ways. From the MySQL docs, it should be as simple as:

$query="DELETE FROM a, b, c, d WHERE email = '$email'";
$result = mysqli_query($con, $query) or die(mysqli_error($con));

I have also tried it with backticks, as:

$query="DELETE FROM `a`, `b`, `c`, `d` WHERE email = '$email'";

And I tried concatenating the individual four lines into a long semi-colon delimited string.

Everything but the four individual lines gives me a SQL syntax error:

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 'WHERE email = '[email protected]'' at line 1.

I checked that the db user has DELETE permissions (and of course they do, since it works fine when run individually).

It's relatively simple to do this in separate statements but I'm just sort of baffled as to why it's not working at this point. Would like to solve this just for curiosity's sake.

I did find the mysqli_multi_query() command...is the "...a, b, c, d..." part being seen by mysqli as separate queries?

Thank you for any help you can provide! (Please note I've left out db connection lines as it's connecting fine.) Again...thank you!

Yes, use multi_query Instead of $query1 2 etc. use $query .= for all, but no dot for the first one. See this accepted answer also stackoverflow.com/a/25455064 — Funk Forty Niner
– Funk Forty Niner, Commented Aug 25, 2014 at 18:14
that won't be any different than what he's experiencing now... — T McKeown
– T McKeown, Commented Aug 25, 2014 at 18:16
If you want to delete from multiple tables you have to join them. — Jimmy T.
– Jimmy T., Commented Aug 25, 2014 at 18:17
@Fred-ii- Thank you, this worked. I concatenated the separate statements rather than trying to write one, then used mysqli_multi_query. If you would like to write this as an answer I'd be happy to mark it! :-) — user1149499
– user1149499, Commented Aug 25, 2014 at 18:32

Funk Forty Niner · Accepted Answer · 2014-08-25 18:35:25Z

2

As per OP's request:

"@Fred-ii- Thank you, this worked. I concatenated the separate statements rather than trying to write one, then used mysqli_multi_query. If you would like to write this as an answer I'd be happy to mark it!"

Yes, use multi_query

Instead of $query1, $query2 etc. Concatenate it just as the manual shows.

Use $query .= for all of them, but don't use a dot for the first one.

answered Aug 25, 2014 at 18:35

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VMai · Accepted Answer · 2014-08-25 18:40:46Z

1

You can do that with

DELETE a, b, c, d 
FROM a 
LEFT JOIN b ON a.email = b.email
LEFT JOIN c ON a.email = c.email
LEFT JOIN d ON a.email = d.email
WHERE a.email = '$email'

There's a second syntax with USING:

DELETE FROM a, b, c, d 
USING a 
LEFT JOIN b ON a.email = b.email
LEFT JOIN c ON a.email = c.email
LEFT JOIN d ON a.email = d.email
WHERE a.email = '$email'

that should work too.

Note

Requirement: The email to be deleted must have at least one row in table a.

see documentation of DELETE syntax:

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

edited Aug 25, 2014 at 18:40

answered Aug 25, 2014 at 18:31

VMai

10.4k9 gold badges28 silver badges36 bronze badges

3 Comments

M Khalid Junaid Over a year ago

Nope this doesn't work if any 3 tables have same email one is missing then this query will not delete any entries from the 3 tables that contains provided email,for this query there is a restriction that these 4 tables must contains that email

VMai Over a year ago

That's a good point, I've edited my answer and changed to left join. The special email has got to be in the first table though.

M Khalid Junaid Over a year ago

Nearly ok but again a limitation :) +1 for your efforts

vinibarr · Accepted Answer · 2014-08-25 18:25:20Z

0

Actually we are not able to execute "JOIN" deletes into MySQL. A good way to do it would be send all your instructions into the same transaction, something like this:

DELIMITER $$
DELETE FROM TABLE1 WHERE ...;
DELETE FROM TABLE2 WHERE ...;
DELETE FROM TABLE3 WHERE ...;
DELETE FROM TABLE4 WHERE ...;
...
DELIMITER $$

answered Aug 25, 2014 at 18:25

vinibarr

4983 silver badges5 bronze badges

Collectives™ on Stack Overflow

Proper syntax for deleting from multiple tables in MySQL/PHP

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related