2

I'm trying to understand how MYSQL determines single-table and multiple table syntax but I can't find this information. The documentation explains how MYSQL handles the two, but it doesn't explain what determines them.

Documentation says:

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE.

Take this query for example:

DELETE table FROM table
INNER JOIN other ON other.column = table.column
WHERE other.column2 = ?
LIMIT 1

Is this single or multiple table syntax? There's a JOIN, so you could lean toward multiple, but it's only deleting from one table. My other suspicion is that it's determined when multiple WHERE clauses are used for multiple tables. If you could include examples of both in your answer it would be much appreciated!

EDIT :
I'm asking this question because when performing certain DELETE queries with a LIMIT I get an error that you cannot use LIMIT with multiple-table syntax.

EDIT #2: In a nutshell, you cannot use ORDER BY or LIMIT if you are joining tables in a DELETE query.

EternalHour
  • 8,308
  • 6
  • 38
  • 57
  • I guess I wouldn't worry about the terminology. WYou understand the concept here - MySQL can delete from several tables if they're specified after the `DELETE` keyword in a `JOIN`'ed delete statement – Michael Berkowski Jan 17 '15 at 22:20
  • Other than that specific page in MySQL's docs, I don't think I've ever read or heard those terms used, but given that you _have to specify `table`_ in the `DELETE` clause, I guess I'd call this "multiple table syntax". If it was truly single-table, it would not be necessary to name `table` after the `DELETE` keyword – Michael Berkowski Jan 17 '15 at 22:22
  • Similar answer is here http://stackoverflow.com/questions/652770/delete-with-join-in-mysql – Seti Jan 17 '15 at 22:23
  • I'm wondering, because as it states in the documentation you can only use `ORDER BY` and `LIMIT` with single-table syntax. It's not just a curiosity. – EternalHour Jan 17 '15 at 22:25
  • Add that to your question then, because that is pertinent information. – Michael Berkowski Jan 17 '15 at 22:27
  • For what it's worth, I just tried to do a `DELETE` from only _one_ of the two joined tables like your example, with an `ORDER BY/LIMIT` clause and MySQL reported a syntax error. So given that, it's "multi-table". – Michael Berkowski Jan 17 '15 at 22:30
  • ^^^ I'll put this as an answer, I guess, because it seems to be unambiguous after testing. – Michael Berkowski Jan 17 '15 at 22:33
  • Understood @MichaelBerkowski, but I would really like to know the criteria. – EternalHour Jan 17 '15 at 22:33
  • @EternalHour The criteria appears to be whether a JOIN is present at all. The presence of any `JOIN` results in a syntax error if either 1) a table is _not_ specified in the `DELETE` clause or 2) an `ORDER BY` is supplied. Is that sufficient? – Michael Berkowski Jan 17 '15 at 22:35
  • @MichaelBerkowski if you believe it's based strictly on `JOIN` it's sufficient. – EternalHour Jan 17 '15 at 22:40
  • @EternalHour Okay, I did a whole bunch of tests and it looks like the first `t` in `DELETE t FROM t...` is really the thing that makes a difference. – Michael Berkowski Jan 17 '15 at 22:58

2 Answers2

1

MySQL's documentation states the following

Regarding the "single-table" synatax:

If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted.

Regarding the "multi-table" syntax:

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

Some testing is revealing in the matter of the ORDER BY limitations.

This is a valid single-table DELETE statement with an ORDER BY:

DELETE FROM table
WHERE somecol = 'someval'
ORDER BY timestamp LIMIT 2

A similar query with an explicit joining condition against one other table results in a syntax error on the ORDER BY, despite only one of the tables being targeted for deletion:

DELETE table1 
FROM table1 JOIN table2 ON table1.id = table2.id
WHERE somecol = 'someval'
ORDER BY timestamp LIMIT 2

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY timestamp LIMIT 2' at line 1

Specifying the same query with an implicit join fails the same way

DELETE table1 
FROM table1, table2
WHERE
  table1.id = table2.id
  AND somecol = 'someval'
ORDER BY timestamp LIMIT 2

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY timestamp LIMIT 2' at line 1

Specifying no table after DELETE for deletion in a joined (multi-table) statement so it looks more like a single-table syntax is also a syntax error

DELETE /* no table named here */
FROM table1 JOIN table2 ON table1.id = table2.id
WHERE somecol = 'someval'
ORDER BY timestamp LIMIT 2

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE somecol

It's the table named in the DELETE clause:

Finally, using only one table, but with the mult-table syntax (naming a table after the DELETE keyword) does not permit an ORDER BY, so the true identifying difference here appears to be tables named in the DELETE clause to distinguish multi-table from single-table:

This query only involves one table (without a join), but produces a syntax error:

/* name table1 in the DELETE clause */
DELETE table1
/* but not other table is joined */
FROM table1
WHERE somecol = 'someval'
ORDER BY id LIMIT 2

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY id LIMIT 1

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
0

If you specify that you DELETE from only 1 table and there are no JOINs involved in your DELETE statement, then it's a single-table syntax. Otherwise, it's a multi-table syntax. I am pretty sure that's what the doc page means.

I think a proof for this interpretation is that down there the page says:

The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.8.2, "JOIN Syntax".

Note that in the single-table syntax there's no table_references element.

peter.petrov
  • 38,363
  • 16
  • 94
  • 159