我有一个 SQL 删除查询

Question

有 2 个表：report（其主键为 reportId 和一个名为 migerated 的位字段）和 report_detail（其外键为 reportId）。我想从report_detail中删除所有具有reportId的行，该reportId在报告表中已迁移= 1。这是选择我想要的所有行的选择查询：

select * 
from report r inner join report_detail d 
    on r.reportId = d.reportId 
where migrated = 1

此删除查询会执行我想要的操作还是我正在执行的操作有事吗？

delete from report_detail
where exists(
    select * 
    from report r inner join report_detail d 
        on r.reportId = d.reportId 
    where migrated = 1
)

Answer 1

DELETE FROM report_detail
WHERE 
    report_detail.reportId IN
    (
        SELECT reportId 
        FROM report 
        WHERE migrated = 1
    )

Answer 2

delete from report_detail d 
inner join report r 
on r.reportId = d.reportId 
where migrated = 1

Answer 3

这可能会删除表中的所有内容。

试试这个：

delete d 
from report_detail d 
inner join report r  
    on r.reportId = d.reportId  
where migrated = 1

Answer 4

MySQL 有一种方法可以从特定表删除，而与其他表连接：

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

或者：

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;