插入许多返回 id 的行，并在另一个表中更新该 id

Question

我正在使用PostgreSQL，我的sql结构：

CREATE TEMP TABLE users (
    id_user serial,
    user_name varchar,
    id_user_description int
);
CREATE TEMP TABLE user_description  (
    id_user_description serial,
    age int
);

users表中有一些用户：

INSERT INTO users (user_name)
SELECT column1
FROM (
    VALUES
    ('John'),
    ('Amanda')
) t;

我正在尝试将数据插入表user_description，我还需要将插入的行 ID 更新到表 users .我的查询是这样的：

WITH inserted_user_description AS (
    INSERT INTO user_description (age)
    SELECT age
    FROM (
        SELECT users.id_user,
            t.column1 AS age,
            t.column2 AS user_name
        FROM (
            VALUES
            (21, 'John'),
            (28, 'Amanda')
        ) t
        INNER JOIN users ON users.user_name = t.column2
    ) tt
    RETURNING id_user_description, tt.id_user
)
UPDATE users SET id_user_description = t.id_user_description
FROM (
    SELECT id_user_description, id_user
    FROM inserted_user_description 
) t
WHERE users.id_user = t.id_user;

但是我得到错误：

错误：缺少表"tt"的 FROM 子句条目 第 15 行：返回id_user_description、tt.id_user

我该如何解决这个问题？

Answer 1

这是一个有效的 SQL 代码段，说明了它是如何工作的。您有 2 张表a和 b 。您希望在 a 中插入行时更新b。

a和b架构：

CREATE TABLE a (
    id serial unique,
    some_int int
);
CREATE TABLE b (
    id serial,
    a_id int,
    some_date timestamp
);

让我们在b中插入一些行以匹配我们将在a中插入的行(它们是我们将更新的行(：

INSERT INTO b (a_id, some_date)
SELECT generate_series, null
FROM generate_series(1, 100);

现在，这里是如何在a中插入行并在b中更新等效行的方法：

WITH inserted as (
    INSERT INTO a (some_int)
    SELECT *
    FROM generate_series(1, 10)
    RETURNING id
)
UPDATE b
SET some_date = NOW()
FROM inserted i
WHERE i.id = b.a_id
;

如您所见，10 行插入a,10 行在b中更新：

test=# SELECT * FROM a;
 id | some_int 
----+----------
  1 |        1
  2 |        2
  3 |        3
  4 |        4
  5 |        5
  6 |        6
  7 |        7
  8 |        8
  9 |        9
 10 |       10
(10 rows)
test=# SELECT * FROM b WHERE some_date IS NOT NULL;
 id | a_id |         some_date          
----+------+----------------------------
  1 |    1 | 2017-03-16 17:48:32.257217
  2 |    2 | 2017-03-16 17:48:32.257217
  3 |    3 | 2017-03-16 17:48:32.257217
  4 |    4 | 2017-03-16 17:48:32.257217
  5 |    5 | 2017-03-16 17:48:32.257217
  6 |    6 | 2017-03-16 17:48:32.257217
  7 |    7 | 2017-03-16 17:48:32.257217
  8 |    8 | 2017-03-16 17:48:32.257217
  9 |    9 | 2017-03-16 17:48:32.257217
 10 |   10 | 2017-03-16 17:48:32.257217
(10 rows)

更新：

在您的特定情况下，这就是我认为您的查询应该的样子(没有架构总是很难编写查询！

WITH inserted_user_description AS (
    INSERT INTO user_description (age, <...>)
    SELECT u.id_user,
        t.column1 AS age,
        <...>
        t.column8 AS user_name
    FROM (
        VALUES
        (21, <...> ,'John'),
        (28, <...> ,'Amanda'),
        <...>
    ) t
    JOIN users u ON u.user_name = t.user_name
    RETURNING id_user_description, u.id_user
)
UPDATE users
SET id_user_description = t.id_user_description
FROM inserted_user_description t
WHERE users.id_user = t.id_user;