PostgreSQL:忽略左外自连接的更新 [英] PostgreSQL: update with left outer self join ignored

问题描述

我想用 TRUE 更新列 leaf_category，其中类别不是父类别.它作为一个选择语句工作:

I want to update the column leaf_category with TRUE where the category is not a parent category. It works as a select statement:

 select 
     c1.id, c1.name, c1.slug, c1.level, c2.parent_id, c2.name, c2.slug, c2.level 
 from
     catalog_category c1 
 left outer join 
     catalog_category c2 on 
     (c1.id = c2.parent_id)
 where 
     c2.parent_id is null;

但是，相应的UPDATE 将所有列设置为TRUE.

However, the corresponding UPDATE sets all the columns to TRUE.

update catalog_category 
set leaf_category = True
from
    catalog_category c1 
left outer join 
    catalog_category c2 on 
    (c1.id = c2.parent_id)
 where 
     c2.parent_id is null;

这样的 UPDATE 有可能吗?

推荐答案

你只是缺少一个连接 WHERE 子句:

You are just missing a connecting WHERE clause:

UPDATE catalog_category c
SET    leaf_category = true
FROM   catalog_category c1 
LEFT   JOIN catalog_category c2 ON c1.id = c2.parent_id
WHERE  c.id = c1.id
AND    c2.parent_id IS NULL;

这种带有 NOT EXISTS 的表单可能更快，执行相同的操作:

This form with NOT EXISTS is probably faster, doing the same:

UPDATE catalog_category c
SET    leaf_category = true
WHERE  NOT EXISTS (
    SELECT FROM catalog_category c1
    WHERE  c1.parent_id = c.id
    );

UPDATE 手册.

相关:

• 选择不存在的行其他表

这篇关于PostgreSQL:忽略左外自连接的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！