为什么我的 "INSERT INTO" 语句不起作用？

Question

为什么我的 "INSERT INTO" 语句不起作用？

3

我正在使用Apache DerbyDB（db2）和SQL Workbench作为环境。我有1万条数据记录，想要创建一个带有维度表和事实表的星型模式。

dwh_price_paid_records = data table
dm_price_paid_records = data mart table (facts table)
dim_time =  data mart table (dimension)

DWH_PRICE_PAID_RECORDS.TRANSACTION_ID = VARCHAR(50)
DWH_PRICE_PAID_RECORDS.PRICE = INTEGER
DWH_PRICE_PAID_RECORDS.DATE_OF_TRANSFER = DATE
DIM_TIME.DATE_ID = Primary key of dim_time (autoincrement start with 1 increment by 1)
DM_PRICE_PAID_RECORDS.DATE_ID = Foreign key NOT NULL from DIM_TIME

在事实表（DM_PRICE_PAID_RECORDS）中，我只想要主键、价格（事实）和ID，并与维度表建立关系，但无效。

INSERT INTO DM_PRICE_PAID_RECORDS (TRANSACTION_ID, PRICE, DATE_ID) VALUES (SELECT TRANSACTION_ID FROM DWH_PRICE_PAID_RECORDS, SELECT PRICE FROM DWH_PRICE_PAID_RECORDS, SELECT DATE_ID FROM DIM_TIME join DWH_PRICE_PAID_RECORDS on DIM_TIME.DATE = DWH_PRICE_PAID_RECORDS.DATE_OF_TRANSFER WHERE DIM_TIME.DATE = 
DWH_PRICE_PAID_RECORDS.DATE_OF_TRANSFER);

我期望得到的结果如下所示:

TRANSACTION_ID | PRICE | DATE_ID

1 | 2324 | 423

2 | 52315 | 234

但我收到了一个德语错误，大意是“VALUES子句必须至少有一个元素。不允许空元素。”

非常感谢！

Flippi

- Flippi

2

正如Gordon所说，这部分是您的语法问题。在那里只能有一个select子句，并且它应该导致您想要放入表中的行 - 匹配所需列的数量。 - Rob Wilson

2

VALUES 子句的语法是 (<列>[, 下一列])[, 下一行]。你的语句试图将多行放入单行元素中。 - Clockwork-Muse

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

我认为您想要的是INSERT . . . SELECT：

INSERT INTO DM_PRICE_PAID_RECORDS (TRANSACTION_ID, PRICE, DATE_ID)
    SELECT ppr.TRANSACTION_ID, ppr.PRICE, t.DATE_ID
    FROM DWH_PRICE_PAID_RECORDS ppr JOIN
        DIM_TIME t 
        ON t.DATE = ppr.DATE_OF_TRANSFER ;