在不存在的地方插入记录

Question

INSERT INTO CUST_ID_HISTORY(CUST_ID,PREVIOUS_CUST_ID,UPDATE_DATE) 
SELECT CUST_ID,PREVIOUS_CUST_ID,UPDATE_DATE
FROM CUST_ID_HISTORY WHERE NOT EXISTS (SELECT 1 FROM CUST_ID_HISTORY WHERE
CUST_ID='SCB301' AND PREVIOUS_CUST_ID='SCB201');

上面的查询插入一条新记录，当表中不存在插入的值时，CUST_ID_HISTORY。我遇到提供的值的错误ORA-00001: unique constraint，但表中不存在这两个值。

Answer 1

您的语句从CUST_ID_HISTORY中进行选择并插入到CUST_ID_HISTORY 中。换句话说，当没有记录CUST_ID != 'SCB301'和PREVIOUS_CUST_ID != 'SCB201'时，它会尝试为CUST_ID_HISTORY中的每个记录创建重复记录。据推测，这是表的主键，因此 ORA-00001 唯一键冲突。

如果您试图实现的是将这些值插入CUST_ID_HISTORY记录，则可能需要一个合并语句。像这样：

merge into CUST_ID_HISTORY tgt
using ( select 'SCB301' as id, 'SCB201' as prev_id
from dual ) src
on ( src.id = tgt.cust_id 
and src.prev_id = tgt.prev_cust_id)
when not matched then
insert (cust_id, prev_cust_id, update_date)
values (src.id, src.prev_id, sysdate)
/

Answer 2

查询：

INSERT INTO CUST_ID_HISTORY(CUST_ID,PREVIOUS_CUST_ID,UPDATE_DATE) 
SELECT CUST_ID,PREVIOUS_CUST_ID,UPDATE_DATE
FROM CUST_ID_HISTORY

将复制CUST_ID_HISTORY表的所有行。

添加过滤器：

WHERE NOT EXISTS (
SELECT 1
FROM CUST_ID_HISTORY
WHERE CUST_ID='SCB301' AND PREVIOUS_CUST_ID='SCB201'
);

将检查表中是否存在现有行CUST_ID='SCB301' AND PREVIOUS_CUST_ID='SCB201'如果该行不存在，则CUST_ID_HISTORY表中的所有行都将重复，如果存在这样的行，则该语句将插入零行。

因此，您的查询将复制所有行或不执行任何操作。

如果要插入单个新行，请检查它是否存在，然后：

INSERT INTO CUST_ID_HISTORY(CUST_ID,PREVIOUS_CUST_ID,UPDATE_DATE) 
SELECT 'SCB301',
'SCB201',
SYSDATE   -- The current date/time
FROM   DUAL
WHERE NOT EXISTS (
SELECT 1
FROM CUST_ID_HISTORY
WHERE CUST_ID='SCB301' AND PREVIOUS_CUST_ID='SCB201'
);

或：

MERGE INTO CUST_ID_HISTORY dst
USING (
SELECT 'SCB301' AS CUST_ID,
'SCB201' AS PREVIOUS_CUST_ID
FROM   DUAL
) src
ON (   src.CUST_ID          = dst.CUST_ID
AND src.PREVIOUS_CUST_ID = dst.PREVIOUS_CUST_ID )
WHEN NOT MATCHED THEN
INSERT ( CUST_ID, PREVIOUS_CUST_ID, UPDATE_DATE ) 
VALUES ( src.CUST_ID, src.PREVIOUS_CUST_ID, SYSDATE );

这将检查是否存在现有行，如果不存在，则插入一行。