CODEWITHSUNDEEP
mysql
Geo
Geo

Reputation: 13006

DELETE statement with ALIAS and LIMIT

Very similar to this question, just a clean cut.

Goal:

I need to use a DELETE statement with ALIAS and LIMIT. Optionally with USING, WHERE, or anything else, other workarounds as long as it works.

Cases 4, 6, 7, 8 would potentially be the solution for this question, but each of them returns an ERROR.

Cases 1, 2, 3, and 5 are shown just for the sake of this example (to fill up the matrix if you will).

Schema:

CREATE TABLE test (id int(10));
INSERT INTO test VALUES (1), (2), (3);

1) no alias, no limit - WORKS

DELETE FROM test;

2) no alias, limit - WORKS

DELETE FROM test LIMIT 1;

3) alias, no limit - ERROR

DELETE FROM test t;

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 't' at line 2

4) alias, limit (would be SOLUTION) - ERROR

DELETE FROM test t LIMIT 1;

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 't LIMIT 1' at line 2

5) alias+USING, no limit - WORKS

DELETE FROM t USING test t;

6) alias+USING, limit (would be SOLUTION) - ERROR

DELETE FROM t USING test t LIMIT 1;

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 'LIMIT 1' at line 2

7) alias, limit, where (would be SOLUTION) - ERROR

DELETE FROM test t WHERE 1=1 LIMIT 1;

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 't WHERE 1=1 LIMIT 1' at line 1

8) alias+USING, limit, where (would be SOLUTION) - ERROR

DELETE FROM t USING test t WHERE 1=1 LIMIT 1;

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 'LIMIT 1' at line 1

Upvotes: 2

Views: 387

Answers (1)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

And what about this :

DELETE FROM t USING test t INNER JOIN (SELECT id FROM test LIMIT 1) t2 ON t.id = t2.id

Upvotes: 1

Related Questions