时间戳

首页 > 解决方案 > 多表插入到Oracle中具有主键的表中

sql - 多表插入到Oracle中具有主键的表中

问题描述

我正在尝试使用单个表将数据插入到不同的表中，并且我要插入的表具有主键。

谁能指导我如何修复错误？

INSERT
ALL
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1995' THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1996' THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN TO_CHAR(L_SHIPDATE,'YYYY') = '1997' THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)


SELECT distinct L_PARTKEY, L_EXTENDEDPRICE, L_SHIPDATE 
FROM LINEITEM;

第 1 行出现错误：ORA-00001：违反唯一约束 (TPCHR.YEAR95_PKEY)

这是表定义

CREATE TABLE YEAR1995
(
    Y95_PARTKEY NUMBER(12) NOT NULL,
    Y95_PRICE   NUMBER(12) NOT NULL,
    CONSTRAINT YEAR95_PKEY PRIMARY KEY (Y95_PARTKEY, Y95_PRICE)  
);

标签： sqloracle

解决方案

您l_shipdate不是唯一的，因此您的查询会在同一年内获得多个结果。您可以将年份提取移至查询：

INSERT
ALL
WHEN T_YEAR = 1995 THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN T_YEAR = 1996 THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)
WHEN T_YEAR = 1997 THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, L_EXTENDEDPRICE - 0.1*L_EXTENDEDPRICE)


SELECT distinct extraxt(YEAR from L_SHIPDATE) AS T_YEAR, L_PARTKEY, L_EXTENDEDPRICE
FROM LINEITEM;

由于每个价格的计算都是相同的，因此您也可以在查询中执行此操作：

INSERT
ALL
WHEN T_YEAR = 1995 THEN
 INTO YEAR1995
  VALUES (L_PARTKEY, T_EXTENDEDPRICE)
WHEN T_YEAR = 1996 THEN
 INTO YEAR1996
  VALUES (L_PARTKEY, T_EXTENDEDPRICE)
WHEN T_YEAR = 1997 THEN
 INTO YEAR1997
  VALUES (L_PARTKEY, T_EXTENDEDPRICE)


SELECT distinct extraxt(YEAR from L_SHIPDATE) AS T_YEAR,
  L_PARTKEY,
  0.9 * L_EXTENDEDPRICE AS T_PRICE
FROM LINEITEM;

我建议您在语句中也包含目标表的列名：

INSERT
ALL
WHEN T_YEAR = 1995 THEN
 INTO YEAR1995 (Y95_PARTKEY, Y95_PRICE)
  VALUES (L_PARTKEY, T_EXTENDEDPRICE)
...

推荐阅读