当引用的父项在同一个表中不存在时，mysql UPDATE 字段为 NULL

Question

我需要编写一个棘手的查询，经过大量研究后，我找不到适合我情况的解决方案。

我正在处理一个现有的数据库，我需要在其中引用可以有子委员会的委员会，因此委员会表如下所示:

+----+--------------------+------------------+
| id | name               | parent_id        |
+----+--------------------+------------------+
|  1 | comm1              | NULL             |
|  2 | comm2              | 1                |
|  3 | comm3              | 1                |
|  4 | comm4              | 5                |
+----+--------------------+------------------+

我需要添加一个外键，让parent_id字段引用实际父级的id字段

ALTER TABLE committee
ADD CONSTRAINT fk_parent_id 
FOREIGN KEY (parent_id )
REFERENCES committee(id);

但这失败了(无法添加或更新子行:外键约束失败)因为在数据中一些 parent_id 字段引用委员会已被删除。

因此，为了能够添加此约束，我想将引用一个不存在的委员会作为父委员会的 parent_id 字段设置为 NULL。我想做类似的事情:

UPDATE committee c1
SET c1.parent_id = NULL
WHERE NOT EXISTS
        (
        SELECT * 
        FROM committee c2
        WHERE c2.id = c1.parent_id
        );

但显然这不起作用，因为我不能在子查询中引用 c1。

是否有实现该目标的可行解决方案？预先感谢您的帮助

Answer 1

您可以使用 Left Join 进行“自连接”:

UPDATE committee AS c1
LEFT JOIN committee AS c2 ON c2.id = c1.parent_id 
SET c1.parent_id = NULL
WHERE c2.id IS NULL 

我们可以通过不考虑那些 parent_id 已经是 null 的行来进一步优化它。

UPDATE committee AS c1
LEFT JOIN committee AS c2 ON c2.id = c1.parent_id 
SET c1.parent_id = NULL
WHERE c2.id IS NULL AND 
      c1.parent_id IS NOT NULL