MySql 8 delete subquery with limit

Question

I have a problem with this query:

DELETE FROM tableA
WHERE idTableA IN
(SELECT tA.idTableA
    FROM tableB tB
    LEFT JOIN tableA tA ON tB.idTableB = tA.idTableA
    WHERE tB.campoX = 100 LIMIT 1);

Error response: Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

EXAMPLE DATA:

TableB 10 rows (it's a table master)

TableA 50 rows (it's a table of details of tableB)

I would like to delete all records detail of tableA related to master (tableB) with condition filter on tableB and limited for 1 row ( this limit is only to measure the execution time for one delete row)

version of MySql: 8

some idea? tk

Idea: how about you give us more details? Like the MySQL version/setup you're using that this error obviously mentions — t1f, Mar 27 '20 at 11:00
Does this answer your question? [MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery](https://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu) — P.Salmon, Mar 27 '20 at 11:02
`LIMIT` without `ORDER BY` makes no sense. What is the logic for choosing just one record from the subquery? — Tim Biegeleisen, Mar 27 '20 at 11:03
'this limit is only to measure the execution time for one delete row' - will give a completely meaningless result. — P.Salmon, Mar 27 '20 at 11:15
@GianfrancoFangano, yes it is not real. Or at least not guaranteed to be representative. Finding the first element might be almost free, but scanning the whole set might be pretty involving. The opposite could also be true. Your initial overhead might be 99% of the cost... — Dr Phil, Mar 27 '20 at 12:20

score 0 · Accepted Answer · answered Mar 27 '20 at 11:03

0

DELETE tableA.*
FROM tableA 
JOIN (SELECT tA.idTableA
      FROM tableB tB
      LEFT JOIN tableA tA ON tB.idTableB = tA.idTableA
      WHERE tB.campoX = 100 LIMIT 1) USING (idTableA);

?

answered Mar 27 '20 at 11:03

Akina

39,301
5
14
25

`LIMIT` without `ORDER BY` makes no sense. – Tim Biegeleisen Mar 27 '20 at 11:03
@TimBiegeleisen Of course. But it's not me who didn't specify the sorting, it's the author... maybe he want to delete ANY one record... – Akina Mar 27 '20 at 11:04
this return for me: Error Code: 1248. Every derived table must have its own alias – Gianfranco Fangano Mar 27 '20 at 11:55
@GianfrancoFangano Add alias to subquery, what a problem? – Akina Mar 27 '20 at 12:00

score 0 · Answer 2 · answered Mar 27 '20 at 11:18

You can use exists to select the rows (this best handles the case when several rows match in b), and use limit in the outer query directly:

delete from tablea a
where exists (select 1 from tableb b where b.campox = 100 and b.idtableb = a.itablea)
order by a.idtablea
limit 1

Note that I added an order by clause to the query, so the record to delete is clearly defined.

score 0 · Answer 3 · answered Nov 01 '22 at 19:19

You can use an alias on subquery and it's easy that way rather than joining it. Here's how it can be done as well

DELETE FROM tableA
WHERE idTableA IN
(select x.idTableA from (
SELECT tA.idTableA
    FROM tableB tB
    LEFT JOIN tableA tA ON tB.idTableB = tA.idTableA
    WHERE tB.campoX = 100 LIMIT 1
) as x);

MySql 8 delete subquery with limit

3 Answers3

Linked

Related