MySQL Delete: two similar codes, only one works

Question

I have two similar SQL queries to solve a problem, though only the second one works.

The problem is:

Considering only muscular disorders, write a query to delete the onsets, which ended up with recovery, of patients that contracted and cured at least two (muscular) diseases.

The database has this structure:

Onset (Patient,Pathology,OnsetDate,RecoveryDate)
Pathology (Name,BodyPart)

This is my first code:

DELETE ES.* 
FROM Onset ES NATURAL JOIN
(
SELECT E.Patient, E.Pathology, E.OnsetDate
FROM Onset E INNER JOIN Pathology P ON E.Pathology=P.Name
WHERE P.BodyPart='Muscles'
      AND E.RecoveryDate IS NOT NULL
      AND 2<=(SELECT COUNT(DISTINCT E2.Pathology)
              FROM Onset E2 INNER JOIN Pathology P2 ON E2.Pathology=P2.Name
              WHERE P2.BodyPart='Muscles'
                    AND E2.Patient=E.Patient
                    AND E2.RecoveryDate IS NOT NULL
             )
) AS D;

This is my second code:

DELETE E.* FROM Onset E 
INNER JOIN Pathology PA ON E.Pathology = PA.Name
NATURAL JOIN(
    SELECT E2.Patient
    FROM Onset E2 INNER JOIN Pathology P ON E2.Pathology = P.Name
    WHERE E2.RecoveryDate IS NOT NULL
        AND P.BodyPart = 'Muscles'
    GROUP BY E2.Patient
    HAVING COUNT(DISTINCT E2.Pathology) >= 2
) AS D
WHERE PA.BodyPart = 'Muscles'
   AND E.RecoveryDate IS NOT NULL;

The second code works fine, instead the first one returns me the common error:

Error Code: 1093. You can't specify target table 'ES' for update in FROM clause

I know that it happens when you try to delete from a table that you use inside a subquery, and that you can bypass this using a derived table. Though, both my codes use Onset inside a subquery, and both use a derived table. So, why the first one doesn't work, while the second does?

Thanks in advance!

`DELETE ES.* `? On second code you try to delete `E`, not `ES`. Or I don't understand something — Max, Jun 05 '16 at 10:07
@Max First query the table is aliased as `ES` and second one as `E` .. — sagi, Jun 05 '16 at 10:08
@leoll2 Here is some explanation of error you got – http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Max, Jun 05 '16 at 10:13
And leoll2 , the answer you your question is in his link. The use of the sub query -> `2 <= (SELECT..)` is the cause of the problem. — sagi, Jun 05 '16 at 10:16
@sagi The fact that 2<=(SELECT..) is inside a derived table doesn't matter at all? — leoll2, Jun 05 '16 at 10:19
Without knowing the layouts of your tables it might be an idea to try to change the NATURAL JOIN and test it with a more explicit join? And the check on "RecoveryDate IS NOT NULL" could probably be put in the inner query? — LukStorms, Jun 05 '16 at 10:27

score 0 · Answer 1 · answered Jun 05 '16 at 11:10

In delete clause remove the from clause

 DELETE ES  
 NATURAL JOIN
 (
 SELECT E.Patient, E.Pathology, E.OnsetDate
 FROM Onset E INNER JOIN Pathology P ON E.Pathology=P.Name
 WHERE P.BodyPart='Muscles'
      AND E.RecoveryDate IS NOT NULL
      AND 2<=(SELECT COUNT(DISTINCT E2.Pathology)
              FROM Onset E2 INNER JOIN Pathology P2 ON E2.Pathology=P2.Name
              WHERE P2.BodyPart='Muscles'
                    AND E2.Patient=E.Patient
                    AND E2.RecoveryDate IS NOT NULL
             )
 ) AS D;

be sure you have a proper (where) condition;

MySQL Delete: two similar codes, only one works

1 Answers1