merge into memployee a using (select * from employee where sex='男') b on (a.id=b.id) when matched then update set a.information1=b.information1, a.information2=b.information2 when not matched then insert (a.id,a.sex,a.information1,a.information2) values (b.id,b.sex,b.information1,b.information2); merge into wemployee a using (select * from employee where sex='女') b on (a.id=b.id) when matched then update set a.information1=b.information1, a.information2=b.information2 when not matched then insert (a.id,a.sex,a.information1,a.information2) values (b.id,b.sex,b.information1,b.information2);
如果要求employee表被更新的时候,自动更新另两个表 可以设置一个触发器
create trigger employee_update after update or insert on employee begin merge into memployee a using (select * from employee where sex='男') b on (a.id=b.id) when matched then update set a.information1=b.information1, a.information2=b.information2 when not matched then insert (a.id,a.sex,a.information1,a.information2) values (b.id,b.sex,b.information1,b.information2); merge into wemployee a using (select * from employee where sex='女') b on (a.id=b.id) when matched then update set a.information1=b.information1, a.information2=b.information2 when not matched then insert (a.id,a.sex,a.information1,a.information2) values (b.id,b.sex,b.information1,b.information2); end;
楼上所言极是,略改一下: merge into memployee a using (select * from employee where sex='男') b on (a.id=b.id) when matched then update set a.information1=b.information1, a.information2=b.information2 where a.updatetime <> b.updatetime when not matched then insert (a.id,a.sex,a.information1,a.information2) values (b.id,b.sex,b.information1,b.information2); merge into wemployee a using (select * from employee where sex='女') b on (a.id=b.id) when matched then update set a.information1=b.information1, a.information2=b.information2 where a.updatetime <> b.updatetime when not matched then insert (a.id,a.sex,a.information1,a.information2) values (b.id,b.sex,b.information1,b.information2);
更正,触发器里不能有commit... 要自己提交
是不是所有的触发器里,都不能有commit?
对,不能有commit 雨捷的想法不错,表如果大的话加个条件能提高效率 可是那么写会有语法错误 可以改这一句 merge into memployee a using (select * from employee where sex='男') b on (a.id=b.id) 改成 merge into memployee a using (select a1.* from employee a1 left join memloyee b1 on a1.id=b1 and a1.sex='男' and a1.updatetime<>b1.updatetime) b on (a.id=b.id)
烧糊涂了,怎么用left join merge into memployee a using (select * from employee where sex='男' minus select a1.* from employee a1 inner join memloyee b1 on a1.id=b1.id and a1.updatetime=b1.updatetime) b on (a.id=b.id)
using (select * from employee where sex='男') b
on (a.id=b.id)
when matched then
update
set
a.information1=b.information1,
a.information2=b.information2
when not matched then
insert
(a.id,a.sex,a.information1,a.information2)
values
(b.id,b.sex,b.information1,b.information2);
merge into wemployee a
using (select * from employee where sex='女') b
on (a.id=b.id)
when matched then
update
set
a.information1=b.information1,
a.information2=b.information2
when not matched then
insert
(a.id,a.sex,a.information1,a.information2)
values
(b.id,b.sex,b.information1,b.information2);
可以设置一个触发器
after update or insert
on employee
begin merge into memployee a
using (select * from employee where sex='男') b
on (a.id=b.id)
when matched then
update
set
a.information1=b.information1,
a.information2=b.information2
when not matched then
insert
(a.id,a.sex,a.information1,a.information2)
values
(b.id,b.sex,b.information1,b.information2); merge into wemployee a
using (select * from employee where sex='女') b
on (a.id=b.id)
when matched then
update
set
a.information1=b.information1,
a.information2=b.information2
when not matched then
insert
(a.id,a.sex,a.information1,a.information2)
values
(b.id,b.sex,b.information1,b.information2);
end;
merge into memployee a
using (select * from employee where sex='男') b
on (a.id=b.id)
when matched then
update
set
a.information1=b.information1,
a.information2=b.information2
where a.updatetime <> b.updatetime
when not matched then
insert
(a.id,a.sex,a.information1,a.information2)
values
(b.id,b.sex,b.information1,b.information2);
merge into wemployee a
using (select * from employee where sex='女') b
on (a.id=b.id)
when matched then
update
set
a.information1=b.information1,
a.information2=b.information2
where a.updatetime <> b.updatetime
when not matched then
insert
(a.id,a.sex,a.information1,a.information2)
values
(b.id,b.sex,b.information1,b.information2);
更正,触发器里不能有commit...
要自己提交
雨捷的想法不错,表如果大的话加个条件能提高效率
可是那么写会有语法错误
可以改这一句
merge into memployee a
using (select * from employee where sex='男') b
on (a.id=b.id)
改成
merge into memployee a
using (select a1.* from employee a1 left join memloyee b1 on a1.id=b1 and a1.sex='男' and a1.updatetime<>b1.updatetime) b
on (a.id=b.id)
merge into memployee a
using (select * from employee where sex='男' minus
select a1.* from employee a1 inner join memloyee b1 on a1.id=b1.id and a1.updatetime=b1.updatetime) b
on (a.id=b.id)
inner join内连接
left join左外连接。因为用左外连接的话,居左的那个表的记录还是会被全部列出,在这里就没有意义
详细的还是看看书本里介绍的吧