PostgreSQL 从子查询更新

Question

我有以下 PostgreSQL 表:

CREATE TABLE users (
    user_id INTEGER DEFAULT nextval('users_user_id_seq') NOT NULL,
    user_old_id CHARACTER VARYING(36),
    created_by INTEGER,
    created_by_old character varying(36),
    last_updated_by INTEGER,
    last_updated_by_old character varying(36),
    CONSTRAINT users_pkey PRIMARY KEY (user_id)
);

根据这个表中的数据我需要更新:

1. created_by 字段，每一行都来自此表的 user_id其中 created_by_old = user_old_id 请注意 created_by_old 可以为 NULL，因此在这种情况下必须避免。

2. last_updated_by 字段与此表中的每个 user_idlast_updated_by_old = user_old_id 请注意 last_updated_by_old 可以为 NULL，因此在这种情况下必须避免。

这是一个示例数据:

实际:

user_id | user_old_id | created_by | created_by_old | last_updated_by | last_updated_by_old 
--------------------------------------------------------------------------------------------
1       | aaa         |            | ccc            |                 | bbb
--------------------------------------------------------------------------------------------
2       | bbb         |            | ddd            |                 | aaa
--------------------------------------------------------------------------------------------
3       | ccc         |            |                |                 | ddd
--------------------------------------------------------------------------------------------
4       | ddd         |            | aaa            |                 | 

预期:

user_id | user_old_id | created_by | created_by_old | last_updated_by | last_updated_by_old 
--------------------------------------------------------------------------------------------
1       | aaa         |  3         | ccc            |   2             | bbb
--------------------------------------------------------------------------------------------
2       | bbb         |  4         | ddd            |   1             | aaa
--------------------------------------------------------------------------------------------
3       | ccc         |            |                |   4             | ddd
--------------------------------------------------------------------------------------------
4       | ddd         |  1         | aaa            |                 | 

我认为它可以用子查询来实现，但现在我自己还不知道如何实现这个查询。请帮忙。

Answer 1

如果你想要一个更高效的更新语句，你可以使用一个派生表来获取你想要的信息作为你的更新语句的来源:

update users u1
  set created_by = t.new_created_by, 
      last_updated_by = t.new_updated_by
from ( 
  select u2.user_id, u2.user_old_id,
         cr.user_id as new_created_by, 
         lu.user_id as new_updated_by
  from users u2
    left join users cr on cr.user_old_id = u2.created_by_old
    left join users lu on lu.user_old_id = u2.last_updated_by_old
) t
where t.user_id = u1.user_id;

通常在from 子句中永远不要重复更新的目标表，但这种情况是不可避免的罕见例子。

但是，如果 user_old_id 也是唯一的，而不仅仅是 user_id，这将正常工作。

在线示例:http://rextester.com/HKM21985