mysql:
这个还是很简单的,一张表插入和更新,两张表的插入和更新都可以。
INSERT INTO table_name (field, field, field) VALUES ('3', '3', '4' ) ON
DUPLICATE KEY UPDATE field= '78', field= '90';
这个是使用语法,下面是使用案例。
INSERT INTO test (id, name, username) VALUES ('3', '3', '4' ) ON
DUPLICATE KEY UPDATE username = '78', name = '90';
oracle:
oracle 这个真的很困难,也确实是我使用的比较少,毕竟现在开发都是用mysql了。
先说两张表的更新,上语法可以借鉴一下(我自己理解的),下面贴案例。
MERGE INTO table_name t1
USING ( select * from table_name2 tbn where tbn.key = value ) t2
ON ( t2.key = t1. key )
WHEN MATCHED THEN
UPDATE
SET t1.field = value, t1.field = value
WHERE t1.key = value
WHEN NOT MATCHED THEN
INSERT (t1.field, t1.field, t1.field )
VALUES ( value, value, value);
贴一下我自己写的案例
MERGE INTO sys."test" test
USING ( SELECT "id" FROM sys."test1" WHERE "id" = '4' ) test2
ON ( test."id" = test2."id" )
WHEN MATCHED THEN UPDATE
SET test."name" = '222',test."username" = '333'
WHEN NOT MATCHED THEN
INSERT ( test."id", test."name",test."username" )
VALUES ( '4', '111', '555' );
如果想用,可以直接拿走,改一下字段和表名就行。
重点来了,一张表更新。这个不写语法了,直接上案例
MERGE INTO sys."test" test USING ( SELECT count( * ) c FROM sys."test" t1 WHERE t1."id" = '7' ) test2 ON ( test2.c > 0 )
WHEN MATCHED THEN
UPDATE
SET test."name" = '222',
test."username" = '333'
WHERE
test."id" = '7'
WHEN NOT MATCHED THEN
INSERT ( test."id", test."name", test."username" )
VALUES
( '7', '111', '555' );
其实重点就是 count(*),然后确认没有这一条,就会新增了。
主要借鉴了这个博主,非常感谢。下面是链接
merge into 操作同一张表