mysql - 使用 3 个表的左连接删除所有

Question

SELECT * FROM property
LEFT JOIN installmentagreement
ON property.PId=installmentagreement.PId
LEFT JOIN installments
ON installmentagreement.Id=installments.Id
LEFT JOIN rentagreement
ON property.PId=rentagreement.PId
WHERE property.PId=39

这是按预期工作的 SELECT 查询。我想删除此 SELECT 查询的结果。我使用了这个查询。

DELETE FROM property( SELECT * FROM property
LEFT JOIN installmentagreement
ON property.PId=installmentagreement.PId
LEFT JOIN installments
ON installmentagreement.Id=installments.Id
LEFT JOIN rentagreement
ON property.PId=rentagreement.PId
WHERE property.PId=39)

还有这个。

DELETE FROM property
LEFT JOIN installmentagreement
ON property.PId=installmentagreement.PId
LEFT JOIN installments
ON installmentagreement.Id=installments.Id
LEFT JOIN rentagreement
ON property.PId=rentagreement.PId
WHERE property.PId=39

但它返回语法错误。我想将 SELECT 查询转换为 DELETE 查询。我用的DB是MySQL，如果有更好的解决方案请指教。请帮忙!

Answer 1

为了清楚起见，我建议使用别名:

SELECT *
FROM property as p
LEFT JOIN installmentagreement as ia
ON p.PId=ia.PId
LEFT JOIN installments as i
ON ia.Id=i.Id
LEFT JOIN rentagreement as ra
ON p.PId=ra.PId
WHERE p.PId=39

然后你可以很容易地转换为 delete 语句，如下所示:

delete p
FROM property as p
LEFT JOIN installmentagreement as ia
ON p.PId=ia.PId
LEFT JOIN installments as i
ON ia.Id=i.Id
LEFT JOIN rentagreement as ra
ON p.PId=ra.PId
WHERE p.PId=39

如果您想在删除 property 记录之前从一些左连接表中删除记录，您可以轻松地更改 delete 语句中的别名:

delete ra
FROM property as p
LEFT JOIN installmentagreement as ia
ON p.PId=ia.PId
LEFT JOIN installments as i
ON ia.Id=i.Id
LEFT JOIN rentagreement as ra
ON p.PId=ra.PId
WHERE p.PId=39