mysql - 从同一表和其他表中的选择指定的表中删除行(内连接)

Question

我需要从数据库中删除一些行，这些行是我使用选择 sql 语句选择的

我尝试过使用别名，但它们也不起作用

DELETE FROM OBSERVATION WHERE OBSERVATION.ID in
(SELECT OBSERVATION.ID from OBSERVATION
inner join SPECIMEN on SPECIMEN.ID=OBSERVATION.SPECIMEN_ID
WHERE SPECIMEN.GENDER="male"and OBSERVATION.VALUE_TERM is not null and OBSERVATION.PARAMETER_STABLE_ID="IMPC_PAT_028_002")

我总是收到错误:

Error in query (1093): You can't specify target table 'OBSERVATION' for update in FROM clause

Answer 1

由于您在 DELETE 和子查询中使用 OBSERVATION 表，因此您可以使用表别名并在 DELETE 语句中正确提及它。

以下查询适用于您的场景:

DELETE OB
FROM OBSERVATION OB
WHERE OB.ID IN (
    SELECT SOB.ID 
    FROM OBSERVATION SOB
    INNER JOIN SPECIMEN SP ON SP.ID = SOB.SPECIMEN_ID
    WHERE SP.GENDER = "male" AND 
          SOB.VALUE_TERM IS NOT NULL AND 
          SOB.PARAMETER_STABLE_ID = "IMPC_PAT_028_002"
)