Search code examples
mysqlsqljoinsql-delete

MySql 8 delete subquery with limit

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

Solution 
  • 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);

    ?