merge into和on duplicate key

来源：互联网发布：列宁格勒知乎编辑：程序博客网时间：2024/05/17 08:16

经常遇到这样的需求，存在这条纪录则更新，不存在则插入。

1.Oracle中使用merge into函数.

MERGE [INTO [schema .] table [t_alias]USING [schema .] { table | view | subquery } [t_alias]ON ( condition )WHEN MATCHED THEN merge_update_clauseWHEN NOT MATCHED THEN merge_insert_clause;

例如：

 merge into test t using (select 1 as id from dual) d on (t.id = d.id) when matched then   update set t.name = 'modify',t.work = 'mwork' when not matched then   insert (id, name, work) values (d.id, 'insert', 'iwork')

2.Mysql中on duplicate key，Mysql根据主键或者唯一索引判断是否存在这条纪录，如果没有的话，会继续插入进去而不会更改

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]       [INTO] tbl_name [(col_name,...)]       VALUES ({expr | DEFAULT},...),(...),...       [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

例如：

INSERT INTO test (id, NAME, WORK)VALUES(1, 'insert', 'iwork') ON DUPLICATE KEY UPDATE   NAME = 'modify',  WORK = 'mwork'

0 0