insert 的那部分数据可以: insert A ( a,a1) select (b,b1) from B where b.pramiy_key not in a.pramiy_key 这样效率不好,最好还是用cursor
可以不用游标。 SQL> select * from A1;C1 C2 -- ---------- A 500SQL> select * from A2;C1 C2 -- ---------- A 20 B 500SQL> update A1 set c2=(select c2 from A2 where A2.C1=A1.C1); --更新1 row updated.SQL> insert into A1 select * from A2 where C1 not in (select C1 from A1); --插入1 row created.SQL> select * from A1;C1 C2 -- ---------- A 20 B 500
在a表中创建insert触发器,在触发器中进行判断 主键相同,则先删除后插入
两条sql语句不就解决了 update A set A.a1=(select b1 from B where A.a=B.b);insert into A select * from B where B.b not in (select A.a from A);
merge into table1 a using(select * from table2 b) on (a.id=b.id) --条件 when matched then --匹配 update set fno='old' when not matched then --不匹配 insert into values(a.id,'new');
请问beckhambobo:要update多个字段怎么写
update tbname set col1='123',col2='456' where ...;
using(select * from table2 b) 我看见一个例子是把 b 放在括号外面的.请问是否一样,
能用merge merge就是干这个使得 merge into table1 a using(select * from table2 b) on (a.id=b.id) --条件 when matched then --匹配 update set fno='old' when not matched then --不匹配 insert into values(a.id,'new');
insert A ( a,a1)
select (b,b1)
from B
where b.pramiy_key not in a.pramiy_key
这样效率不好,最好还是用cursor
SQL> select * from A1;C1 C2
-- ----------
A 500SQL> select * from A2;C1 C2
-- ----------
A 20
B 500SQL> update A1 set c2=(select c2 from A2 where A2.C1=A1.C1); --更新1 row updated.SQL> insert into A1 select * from A2 where C1 not in (select C1 from A1); --插入1 row created.SQL> select * from A1;C1 C2
-- ----------
A 20
B 500
主键相同,则先删除后插入
update A set A.a1=(select b1 from B where A.a=B.b);insert into A select * from B where B.b not in (select A.a from A);
using(select * from table2 b)
on (a.id=b.id) --条件
when matched then --匹配
update set fno='old'
when not matched then --不匹配
insert into values(a.id,'new');
merge就是干这个使得
merge into table1 a
using(select * from table2 b)
on (a.id=b.id) --条件
when matched then --匹配
update set fno='old'
when not matched then --不匹配
insert into values(a.id,'new');
UPDATE SET column = { expr | DEFAULT } [, column = { expr | DEFAULT }]...