如何将数据从另一个表中插入表中，可能存在重复键

I'm attempting to write a conditional query that inserts new rows into a table from data from another table, but if the PK already exists for that row, to update it instead. I came across ON DUPLICATE KEY UPDATE, but can only find examples that work with inserted values rather than data obtained from an inner select statement. Here is an example to illustrate what I'm talking about:

我正在尝试编写一个条件查询,从另一个表的数据中将新行插入到表中,但如果该行已存在PK,则更新它。我遇到了ON DUPLICATE KEY UPDATE,但是只能找到使用插入值而不是从内部select语句获得的数据的示例。这是一个例子来说明我在说什么:

INSERT INTO SCHEMA1.TABLE1 T1
  (T1.ID,
   T1.COLUMN1,
   T1.COLUMN2)
SELECT T2.ID,
       T2.COLUMN1,
       T2.COLUMN2
FROM SCHEMA2.TABLE2 T2
WHERE T2.COLUMN3 = ?)
ON DUPLICATE KEY UPDATE T1.COLUMN1 = T2.COLUMN1;

For some reason, the syntax of the above does not work. The only thing that I can gather from the Oracle docs is that ON DUPLICATE KEY UPDATE can only be used with VALUES -- not a nested SELECT query.

出于某种原因,上述语法不起作用。我可以从Oracle文档中收集的唯一内容是ON DUPLICATE KEY UPDATE只能与VALUES一起使用 - 而不是嵌套的SELECT查询。

2 个解决方案

#1

Thanks to mustaccio and Alex Poole, here is the solution:

感谢mustaccio和Alex Poole,这是解决方案:

MERGE INTO SCHEMA1.TABLE1 T1 USING
(SELECT ID,
   COLUMN1,
   COLUMN2
 FROM SCHEMA2.TABLE2
 WHERE COLUMN3 = ?) T2
ON (T1.ID = T2.ID)
WHEN MATCHED THEN UPDATE SET
  T1.COLUMN1 = T2.COLUMN1,
  T1.COLUMN2 = T2.COLUMN2
WHEN NOT MATCHED THEN INSERT (
  T1.ID,
  T1.COLUMN1,
  T1.COLUMN2
VALUES (
  T2.ID,
  T2.COLUMN1,
  T2.COLUMN2);

#2

Just an alternative whcih you can use instead of MERGE if you have ORACLE version 11 or more. We have some specific HINTS from oracle 11g onwards which are used to ignore duplicate values on unique INDEX. Below snippet is a very small but descriptive example which you can incorporate hope this helps.

如果您有ORACLE 11或更高版本,那么您可以使用替代MERGE的替代方案。我们从oracle 11g开始有一些特定的HINTS用于忽略唯一INDEX上的重复值。下面的代码段是一个非常小但是描述性的示例,您可以将希望纳入其中。

-- Table creation with Primary key as SR_NO
CREATE TABLE DUP_CHECK_KEY
(
SR_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(100 CHAR)
);

Table created

-- Inserting random data in the first go
INSERT INTO DUP_CHECK_KEY
SELECT LEVEL,LEVEL||'Av' FROM dual 
CONNECT BY level < 10;

10 rows inserted.

--Again inserting same data with 2 more unique rows to be inserted.
INSERT /*+ ignore_row_on_dupkey_index(DUP_CHECK_KEY,SYS_C00145520) */ INTO DUP_CHECK_KEY
SELECT LEVEL,LEVEL||'Av' FROM dual 
CONNECT BY level < 12;

2 rows inserted.

Since we have 10 duplicate key values it has ignored the dup values and inserted only 2 unique values.

由于我们有10个重复的键值,因此它忽略了dup值并仅插入了2个唯一值。

#1