我有两个类似的sql查询来解决一个问题,尽管只有第二个可以工作。
问题是:
只考虑肌肉疾病,写一个查询来删除至少两种(肌肉)疾病的患者的onset(最终恢复)。
数据库具有以下结构:

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

这是我的第一个代码:
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;

这是我的第二个密码:
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;

第二个代码运行良好,而第一个代码返回常见错误:
错误代码:1093。不能在中指定要更新的目标表“es”
从句
我知道当您试图从子查询中使用的表中删除时会发生这种情况,并且您可以使用派生表绕过它。
尽管如此,我的两个代码都在子查询中使用起始,并且都使用派生表。那么,为什么第一个不起作用,而第二个呢?
提前谢谢!

最佳答案

在DELETE子句中删除FROM子句

 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;

一定要有适当的条件;

关于mysql - MySQL删除:两个相似的代码,只有一个有效,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/37640640/

10-12 12:52
查看更多