postgresSQL插入多行，从选择查询返回的id [英] postgresSQL insert multiple rows, of id returned from select queries

问题描述

我有一个复杂的查询，该查询连接多个表并返回许多成员ID（第5行）

I have a complex query that join multiple tables and return many member ids (line 5)

对于每个要插入的memberId，我要插入一个memberSegment记录，该记录由memberId（每次插入都是新的）和segmentId（总是相同/未列出）

For each memberId I want to insert a memberSegment record, consisting of the memberId (new for each insert) and a segmentId (always the same/not list)

INSERT INTO db."memberSegment"(
    "memberId",
    "segmentId")
VALUES (
    (select table."memberId" complex query returns many ids ),
    (SELECT id FROM db.segment where "idName" = 'due-for-360')
);

从SO的阅读中可以看出，这是我的解释，但我收到以下错误消息，使我认为查询不希望在两个值中列出。

From reading on SO this is how I interpret it should look, but I am getting following error message, making me think that my query is not expecting a list in either values.

错误：子查询返回的行多于表达式
SQL状态：21000

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

每个查询自身的返回结果如下：

Each query on its' own returns following:

推荐答案

您可以用这个短语作为 INSERT INTO ... SELECT ：

You might be able to phrase this as an INSERT INTO ... SELECT:

INSERT INTO db."memberSegment" (memberId, segmentId)
SELECT
    memberId,
    (SELECT id FROM db.segment WHERE idName = 'due-for-360')
FROM table -- (complex query returns many ids );

这至少可以解决您当前的错误，这是由于查询返回的值大于一个ID。唯一可能的问题是 db.segment 上的子查询是否还返回多个值。如果不行，则上面的方法应该起作用。如果确实返回多个值，则需要重新考虑您的逻辑。

This would at the very least get around your current error, which is stemming from the query returning more than one id. The only possible issue would be if the subquery on db.segment also returns more than a single value. If it does not, then the above should work. If it does return more than one value, then your logic needs to be reconsidered.

这篇关于postgresSQL插入多行，从选择查询返回的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！