我有两个类似的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/