使用选择子查询更新同一表的列 [英] Update a column using a select subquery to the same table

问题描述

示例表如下.我最近添加了is_last_child"列，如果该行是最后一个孩子(或不是父母)，我想将其更新为值 1.我有一个

Sample table below. I recently added the column 'is_last_child' and want to update it to have a value 1 if the row is the last child (or is not a parent). I had a query of

update node set is_last_child=1 where id not in (select parent_id from node);

运行时出现以下错误.您不能在 FROM 子句中为更新指定目标表‘节点’".我试过使用连接，但我确定我只能更新不是父行的行.有没有人有任何想法或遇到过类似的情况?

I get the following error when I run it. "You can't specify target table 'node' for update in FROM clause". I've tried using a join, but I'm sure how exactly I can update only the rows that are not a parent. Any one have any ideas or have run into a similar situation?

id | parent_id | is_last_child
1  |    1      |      0
2  |    1      |      0   
3  |    1      |      0
4  |    2      |      0
5  |    4      |      0
6  |    1      |      0

基本上我想选择 ids 3、5 和 6 并将列 is_last_child 设置为 1.这不是我的架构，有数千行，但上面的表格只是为了简化事情.

Essentially I want to select ids 3, 5, and 6 and set the column is_last_child equal to 1. This isn't my schema and there are thousands of rows, but the table above is just to simplify things.

推荐答案

你想要 UPDATE FROM:

UPDATE N1
SET N1.is_last_child = 1
FROM Node N1
LEFT OUTER JOIN Node N2
    ON N1.ID = N2.Parent_ID
WHERE N2.ID IS NULL

左外连接在概念上与使用 NOT IN 相同，只是它更易于阅读并且您不需要一堆嵌套查询.

The left outer join is conceptually the same as using NOT IN only it's easier to read and you don't need a bunch of nested queries.

这篇关于使用选择子查询更新同一表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！