主页 / 问题 / 1011305
Accepted
triplustri
Asked:2020-08-08 13:53:02 +0000 UTC2020-08-08 13:53:02 +0000 UTC

INSERT 相关数据到多个表中

  • 772

有以下表格：

CREATE TABLE user (
    id integer,
    name varchar(100),
    info varchar(100)
);

CREATE TABLE shop (
    id integer,
    name varchar(100)
);

CREATE TABLE user_shop_m2m (
    user_id integer,
    shop_id integer
);

并假设有数据可以添加新用户：

users = [
    {name: 'John', info: 'Info for John', shops: [1, 2, 3]},
    {name: 'Jane', info: 'Info for Jane', shops: [3, 4, 5]},
]

您需要将此数据添加到上述表格中。有很多数据，在应用程序端循环执行非常慢。您可以通过一个 INSERT 添加“用户”：

INSERT INTO user (
  "name",
  "info"
)
VALUES (
    ("John", "Info for John"),
    ("Jane", "Info for Jane"),
)
ON CONFLICT (
    "name",
)
DO UPDATE SET value=EXCLUDED.value
RETURNING *

但我不知道如何使用另一个 INSERT 将商店数据添加到 user_shop_m2m 连接表。

sql

2 个回答

  1. INSERT INTO user_shop_m2m (user_id, shop_id)
SELECT u.user_id, s.shop_id
FROM ( SELECT user_id 
       FROM user
       WHERE name = 'John'
       /*  AND info = 'Info for John' */ ) u,
     ( SELECT UNNEST(ARRAY[1, 2, 3] shop_id ) s
    • 0
  2. Best Answer

    使用 CTE，您可以执行以下操作：

    WITH new_data(name, info, shop_ids) AS (
  VALUES
  ('John', 'Info for John', ARRAY[1, 2, 3])
, ('Jane', 'Info for Jane', ARRAY[3, 4, 5])
), inserted(user_id, name, info) AS (
  INSERT INTO "user" (name, info)
  SELECT name, info
    FROM new_data
  ON CONFLICT (name) DO
  UPDATE SET name = EXCLUDED.name
  RETURNING id, name, info
), new_references(user_id, shop_id) AS (
  SELECT inserted.user_id, UNNEST(new_data.shop_ids)
    FROM inserted
    JOIN new_data
   USING (name, info)
)
INSERT INTO user_shop_m2m
SELECT * FROM new_references;

    我们获取新数据，将其插入用户表中，为链接表收集数据，然后将其插入。假定您的列是name唯一的。

    • 0

相关问题