MERGE INTO tableA A USING (筛选结果集) B ON (A.XX=B.XX) /*WHEN MATCHED THEN UPDATE SET LOW.KFRST = LOW1.KFRST */ WHEN NOT MATCHED THEN INSERT (FIELD1,..) VALUES (1,...); 你可以搜搜merge into 的yongf
merge into,insert into ...where not exists...都可以,但考虑到并发性,最好设置唯一键吧
加unique约束吧,这样有重复就会插入失败。
会有异常,做下异常处理,类似... exception when dup_val_on_index then --印象中重复的异常是这个,若不确定可用others null;--不做任何操作
查出重复值的SQL,如果有记录说明重复,没有则不重复。 select rowid,username,userpwd from a where a.rowid!=(select max(rowid) from a b where a.username=b.usernameand a.userpwd =b.userpwd );
5楼正确,null是不执行任何操作。
直接用MERGE INTO 不就行了
merge into test a using ( select '1' as name, '女' as sex from dual ) b on (a.name=b.name and a.sex = b.sex) when not matched then INSERT VALUES (1,'2','女');
MERGE INTO tableA A
USING (筛选结果集) B
ON (A.XX=B.XX)
/*WHEN MATCHED THEN
UPDATE
SET LOW.KFRST = LOW1.KFRST */
WHEN NOT MATCHED THEN
INSERT
(FIELD1,..)
VALUES
(1,...);
你可以搜搜merge into 的yongf
exception
when dup_val_on_index then --印象中重复的异常是这个,若不确定可用others
null;--不做任何操作
表 user 有两个字段 username userpwdzhangsan 123
lisi 123username字段是唯一值不能重复 如果重复放弃本次插入 这个插入操作是在C#程序写的 一报错程序端就停住了 现在问题是不能让它停 只能continue;
select rowid,username,userpwd from a where a.rowid!=(select max(rowid) from a b where a.username=b.usernameand a.userpwd =b.userpwd );
直接用MERGE INTO 不就行了
using
(
select '1' as name, '女' as sex from dual
) b
on (a.name=b.name and a.sex = b.sex)
when not matched then
INSERT VALUES (1,'2','女');