用merge可以实现插入时判断 MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.category = 'DVD' WHEN NOT MATCHED THEN INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category != 'BOOKS'获得insert 或 update 记录的次数在执行完insert 或update 后 用sql%rowcount
用merge into,详细用法搜搜。
你在作insert之前先执行这句话,select count(*) into var from XXX where ... 如果var=0,说明不存在,插入 如果var >0,说明存在,则更新。
不知你是在什么情况下应用? 在存储过程里,还是在程序里 但不管怎么说,直接在insert前做个判断就行 select count(*) from tablename where ....
我现在的做法是,不管有没有记录,先删除存在的要插入的记录,然后再插入;执行两次SQL
create table ttt(cid varchar2(100),cname varchar2(100)); insert into ttt select '1','1' from dual union all select '2','2' from dual; select * from ttt
MERGE INTO ttt USING (SELECT '1' cid,'1' cname from dual) T ON (TTT.cid = T.cid) WHEN MATCHED THEN UPDATE SET ttt.cname = T.cname WHEN NOT MATCHED THEN INSERT VALUES (t.cid,T.cName);
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,
p.category = np.category
WHERE p.category = 'DVD'
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category)
WHERE np.category != 'BOOKS'获得insert 或 update 记录的次数在执行完insert 或update 后 用sql%rowcount
如果var=0,说明不存在,插入
如果var >0,说明存在,则更新。
难道我还要建一张临时表??先inert到临时表,再做merge???????
在存储过程里,还是在程序里
但不管怎么说,直接在insert前做个判断就行
select count(*) from tablename where ....
insert into ttt select '1','1' from dual union all select '2','2' from dual;
select * from ttt
MERGE INTO ttt
USING (SELECT '1' cid,'1' cname from dual) T
ON (TTT.cid = T.cid)
WHEN MATCHED THEN UPDATE SET ttt.cname = T.cname
WHEN NOT MATCHED THEN INSERT VALUES (t.cid,T.cName);