避免重复在插入
问题描述:
我必须将数据插入到表,但这个表有两列column_1
和column_2
唯一约束。现在在插入过程中出现以下错误:避免重复在插入
Lookup Error - DB2 Database Error: ERROR [23505] [IBM][DB2/AIX64] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "table_name" from having duplicate values for the index key. SQLSTATE=23505.
如何避免此错误?
答
最后,我使用NOT EXISTS子句来处理solvit。
答
您可以实现“upsert = update或insert”语义。
MERGE INTO employees AS tab
USING (VALUES
(123456,'smith','bob')
) AS merge (id,last_name,first_name)
ON tab.id = merge.id
WHEN MATCHED THEN
UPDATE SET tab.id = merge.id,
tab.last_name = merge.last_name,
tab.first_name = merge.first_name
WHEN NOT MATCHED THEN
INSERT (id,last_name,first_name)
VALUES (merge.id, merge.last_name, merge.first_name)
请参阅https://www.withdata.com/blog/db2/replace-update-or-insert-a-row-into-db2-table-merge-into.html –