SQL-使用WITH在INSERT INTO上声明变量 [英] SQL - Using WITH to declare variable on INSERT INTO

问题描述

在尝试执行 INSERT INTO 时，我试图使用 WITH 声明查询变量。
我正在关注 https://stackoverflow.com/a/16552441/2923526 ，其中给出了以下示例对于 SELECT 查询：

I'm trying to use WITH to declare a variable for a query when doing an INSERT INTO. I'm following https://stackoverflow.com/a/16552441/2923526 which gives the following example for a SELECT query:

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

---

我尝试了以下运气：

---

I tried the following with no luck:

playground> CREATE TABLE foo (id numeric)
CREATE TABLE

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (x)
column "x" does not exist
LINE 1: WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (x)
                                                                ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x)
missing FROM-clause entry for table "consts"
LINE 1: ...consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x)
                                                              ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x) FROM consts
syntax error at or near "FROM"
LINE 1: ...AS (VALUES (2)) INSERT INTO foo VALUES (consts.x) FROM const...
                                                             ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo FROM consts VALUES (consts.x)
syntax error at or near "FROM"
LINE 1: WITH consts (x) AS (VALUES (2)) INSERT INTO foo FROM consts ...
                                                        ^

---

请注意，我是SQL的初学者，因此我希望避免使用暗示使用PLPGSQL的解决方案

---

Note I am begginer to SQL so I'm looking to avoid solutions that imply using PLPGSQL

推荐答案

我认为您想要：

WITH consts (x) AS (VALUES (2)) INSERT INTO foo SELECT x FROM consts

即： WITH 子句创建派生表，然后您可以在主查询中使用它；因此您实际上需要 SELECT ... FROM 公用表表达式。

That is: the WITH clause creates a derived table, that you can then use within the main query; so you actually need to SELECT ... FROM the common table expression.

这篇关于SQL-使用WITH在INSERT INTO上声明变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！