Postgresql插入多个记录的表值

gj3fmq9x 于 2023-04-29 发布在 PostgreSQL

关注(0)|答案(2)|浏览(107)

我需要在一个表（groupL）中插入两行（custId，aName）。custId是常量，而aName是基于嵌套子查询的可变大小的多个记录。
该子查询是：

SELECT aName 
FROM artwork 
WHERE artwork.title IN (SELECT title 
                        FROM classify 
                        WHERE NEW.g_name = classify.g_name);

我试过很多SQL，但我真的不明白。我正在寻找这样的东西，如果我们在aName（name1，name2）中有两行，它会做类似的事情

INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name1 
    WHERE NOT EXISTS (SELECT custIdConstant, aName 
                      FROM groupL 
                      WHERE custId = custIdConstant AND aName = name1);

INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name2 
    WHERE NOT EXISTS (SELECT custIdConstant, aName 
                      FROM groupL 
                      WHERE custId = custIdConstant AND aName = name2);

我该怎么做？任何指导将不胜感激。

postgresql

来源：https://stackoverflow.com/questions/76131480/postgresql-insert-into-table-values-with-multiple-records

2条答案

按热度按时间

nfg76nw01#

你可以在一个Go中完成，但是如果你想比较的话，你需要先有id，当然还有FROM子句

CREATE tABLe groupL(custId int , aName TEXT)

CREATE TABLE

CREATE TABLe aName (name1 TEXT, name2 TEXT)

CREATE TABLE

INSERT INTO aName VALUEs ('A','B')

INSERT 0 1

DO $$
DECLARE  
   custIdConstant integer := 10;  
BEGIN  
INSERT INTO groupL(custId, aName) 
SELECT custIdConstant, name1 FROM aName  WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name1);

INSERT INTO groupL(custId, aName) 
SELECT custIdConstant, name2 FROM aName WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name2);

END $$;

DO

SELECT * FROM groupL

茎	阿纳梅
10个	一个
10个	B

DO $$
DECLARE  
   custIdConstant integer := 10;  
BEGIN  
INSERT INTO groupL(custId, aName) 
SELECT custIdConstant, name1 FROM aName  WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name1);

INSERT INTO groupL(custId, aName) 
SELECT custIdConstant, name2 FROM aName WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name2);

END $$;

DO

SELECT * FROM groupL

茎	阿纳梅
10个	一个
10个	B

SELECT 2

fiddle

赞(0）回复(0）举报 2023-04-29

2nbm6dog2#

我想你只是想通过第二个子查询来检查这些行的存在：

INSERT INTO groupL (custId, aName)
SELECT <custId>, a.aName 
FROM artwork as a
WHERE a.title IN (SELECT c.title 
                  FROM classify as c INNER JOIN NEW ON NEW.g_name = c.g_name)
  AND NOT EXISTS (SELECT 1
                  FROM groupL as g
                  WHERE g.custId = <custId> AND g.aName = a.aName);

您可能还想检查merge。

赞(0）回复(0）举报 2023-04-29

我来回答

Postgresql插入多个记录的表值

2条答案

相关问题

热门标签

最新问答