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_old = user_old_id 的记录，请使用此表中的 user_id 更新 created_by 字段。请注意，created_by_old 可能为NULL，在这种情况下必须避免更新。

2. 对于每一行中 last_updated_by_old = user_old_id 的记录，请使用此表中的 user_id 更新 last_updated_by 字段。请注意，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            |                 | 

我认为可以使用子查询来实现，但我现在无法自己想出如何实现此查询。请帮忙。

- alexanoid

---

请提供数据样本和期望输出。 - sagi

你到目前为止尝试了什么？ - Houari

@sagi，我已经更新了我的问题，附上了示例数据和预期结果。 - alexanoid

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

- user330315

Answer 2

虽然不是很高效，但如果效率不是问题，你可以这样做：

UPDATE users u
SET u.created_by = (SELECT t.user_id FROM users t
                    WHERE u.created_by_old = t.user_old_id),
    u.last_updated_by = (SELECT s.user_id FROM users s
                         WHERE u.last_updated_by = s.user_old_id)

- sagi

---

谢谢。请注意，某些行中的 created_by_old 或 last_updated_by_old 可能为空，因此在这种情况下必须避免更新相应的字段。如何更新此查询以支持此情况？ - alexanoid

为什么？如果它是 NULL，那么结果也将是 NULL。@alexanoid - sagi

谢谢。我尝试了这个查询 UPDATE users u SET u.created_by = (SELECT t.user_id FROM users t WHERE u.created_by_old = t.user_old_id), u.last_updated_by = (SELECT s.user_id FROM users s WHERE u.last_updated_by_old = s.user_old_id) 但是它失败了，出现以下错误：ERROR: column "u" of relation "users" does not exist LINE 1: UPDATE users u SET u.created_by = (SELECT t.user_id FROM use... - alexanoid

我已将u.created_by更新为简单的created_by，现在一切都正常了！谢谢！ - alexanoid