Convert multiples MySQL DELETE in only one

Question

I have the following queries:

DELETE FROM server_N1 where vencimiento <= '2015/01/08';
DELETE FROM server_N2 where vencimiento <= '2015/01/08';
DELETE FROM server_N3 where vencimiento <= '2015/01/08';
...
DELETE FROM server_N11 where vencimiento <= '2015/01/08';

I want to convert theses queries in only one query. I tried the following but it doesn't work:

DELETE
N1, N2, N3, N4, N5, N6, N7, N8, N9, N10, N11 FROM server_N1 N1, server_N2 N2, server_N3 N3, server_N4 N4, server_N5 N5, server_N6 N6, server_N7 N7, server_N8 N8, server_N9 N9, server_N10 N10, server_N11 N11
WHERE
(N1.vencimiento <= '2015/01/08') AND
(N2.vencimiento <= '2015/01/08') AND
(N3.vencimiento <= '2015/01/08') AND
(N4.vencimiento <= '2015/01/08') AND
(N5.vencimiento <= '2015/01/08') AND
(N6.vencimiento <= '2015/01/08') AND
(N7.vencimiento <= '2015/01/08') AND
(N8.vencimiento <= '2015/01/08') AND
(N9.vencimiento <= '2015/01/08') AND
(N10.vencimiento <= '2015/01/08') AND
(N11.vencimiento <= '2015/01/08');

How can I do it?

Edit: The problem is when I trying to join all queries in only one, I receive the following 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 'DELETE FROM server_N2 WHERE vencimiento <= '2015/01/08'; DELETE FROM server_N3 W' at line 1

Edit: Code:

$fechaactual = '2015/01/08';
$delete = mysqli_query($con, "DELETE FROM server_N1 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N2 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N3 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N4 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N5 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N6 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N7 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N8 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N9 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N10 WHERE vencimiento <= '{$fechaactual}'; DELETE FROM server_N11 WHERE vencimiento <= '{$fechaactual}'");

Just wrap the original `delete`s in a transaction, if you want them all to take effect at the same time. — Gordon Linoff, Jan 08 '15 at 12:49
Is it what you are looking for: http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql ? — Marek Stanley, Jan 08 '15 at 12:51
@GordonLinoff: Yes, but when I do it, I receive the following message: 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 'DELETE FROM server_N2 WHERE vencimiento <= '2015/01/08'; DELETE FROM server_N3 W' at line 1 — Fulbito, Jan 08 '15 at 12:55
@Fulbito . . . Can you edit your question with the full code that is generating that error? — Gordon Linoff, Jan 08 '15 at 12:58
@Fulbito . . . mysqli_query() only takes one query. Split them into separate calls. — Gordon Linoff, Jan 08 '15 at 15:43

score 1 · Answer 1 · edited Apr 23 '20 at 20:22

The problem is that one or more of the tables have no rows to delete. Because there are no rows meeting one of the conditions, the result set is empty -- hence nothing is deleted.

If you knew that one of the tables had rows meeting the condition, then you could start with that table and use left join with the conditions in the on clause. However, if you don't know which table has such rows, then the query is harder.

In the end, it is simpler and you probably get better performance to wrap the delete statements in a transaction, so they all take effect at the same time.

Of course, this begs the question why you have 11 tables with the same structure. That is usually a sign of poor database design. The records in those tables should probably all be in a single table, with a column defining the "N" value. Then you wouldn't even need to ask this question -- a simple delete would suffice.

Try this:

DELETE s FROM server_N1 s where vencimiento <= '2015/01/08';

Convert multiples MySQL DELETE in only one

1 Answers1