一个INSERT与多个SELECT [英] One INSERT with multiple SELECT

问题描述

我已经阅读了此，此和

I've already read this, this and this, but I cant make this SQL work:

INSERT INTO main_phrase (description) VALUES ('Mot commun féminin pluriel animaux');
/* ERROR: */
WITH
  t1 AS (
    SELECT id 
    FROM main_phrase 
    WHERE description='Mot commun féminin pluriel animaux'
  ),
  t2 AS (
    SELECT id 
    FROM main_groupecategories 
    WHERE description='Mot commun féminin pluriel animaux'
  )
INSERT 
  INTO main_phrasegroupecategories (phrase_id, groupe_categories_id)
  VALUES                           (t1.id,     t2.id);

我得到:

错误:表t1的FROM子句缺少条目

ERROR: missing entry for the clause FROM for table t1

我想念什么?

推荐答案

假设

• 您要使用相同的description将main_phrase中新插入的行链接到main_groupecategories中的行.
• main_phrase.id是 serial 列.

Assumptions

• You want to link the newly inserted row in main_phrase to the row(s) in main_groupecategories with the same description.
• main_phrase.id is a serial column.

您不能在独立的VALUES表达式中引用任何表(包括CTE)，必须将SELECT与FROM子句一起使用.但是有一个更好的解决方案.见下文.

You cannot refer to any tables (including CTE) in a free-standing VALUES expression, you would have to use SELECT with a FROM clause. But there is a better solution. See below.

使用修改数据的CTE 而是使整个操作更短，更安全，更快:

Use a data-modifying CTE instead to make the whole operation shorter, safer and faster:

WITH p AS (
   INSERT INTO main_phrase (description)
   VALUES ('Mot commun féminin pluriel animaux')  -- provide description once
   RETURNING id, description  -- and reuse it further down
   )
INSERT INTO main_phrasegroupecategories (phrase_id, groupe_categories_id)
SELECT p.id, g.id
FROM   p
JOIN   main_groupecategories g USING (description);

如果要使用新行的任何值，请立即将它们与另一个RETURNING子句一起返回到第二个INSERT.

If you want to use any values of the new rows, have them returned immediately with another RETURNING clause to the second INSERT.

为什么您的(假定)

Why would you have the same description redundantly in both tables of your (presumed) many-to-many relationship? Might be a problem in your database design.

相关:

• PostgreSQL多重插入...多列返回
• SELECT * FROM NEW TABLE等同于Postgres
• 组合带有CASE表达式的数据修改CTE中的INSERT语句
• PostgreSQL multi INSERT...RETURNING with multiple columns
• SELECT * FROM NEW TABLE equivalent in Postgres
• Combining INSERT statements in a data-modifying CTE with a CASE expression

这篇关于一个INSERT与多个SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！