如何使用单个查询更新多个表中的数据？
MySQL示例
UPDATE party p LEFT JOIN party_name n ON p.party_id = n.party_id LEFT JOIN party_details d ON p.party_id = d.party_id LEFT JOIN incident_participant ip ON ip.party_id = p.party_id LEFT JOIN incident i ON ip.incident_id = i.incident_id SET p.employee_id = NULL,c.em_address = 'x@x.org',c.ad_postal = 'x',n.first_name = 'x',n.last_name = 'x' WHERE i.confidential_dt IS NOT NULL
使用Oracle 11g会有什么相同的声明？
谢谢！
RTFM
使用Oracle时似乎单个查询不足：
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2067717
解决方法
/** XXX CODING HORROR... */
根据您的需要,您可以使用可更新的视图.您可以创建基表的视图,并为此视图添加“代替”触发器,并直接更新视图.
一些示例表：
create table party (
party_id integer,employee_id integer
);
create table party_name (
party_id integer,first_name varchar2(120 char),last_name varchar2(120 char)
);
insert into party values (1,1000);
insert into party values (2,2000);
insert into party values (3,3000);
insert into party_name values (1,'Kipper','Family');
insert into party_name values (2,'Biff','Family');
insert into party_name values (3,'Chip','Family');
commit;
select * from party_v;
PARTY_ID EMPLOYEE_ID FIRST_NAME LAST_NAME
1 1000 Kipper Family
2 2000 Biff Family
3 3000 Chip Family
…然后创建一个可更新的视图
create or replace view party_v
as
select
p.party_id,p.employee_id,n.first_name,n.last_name
from
party p left join party_name n on p.party_id = n.party_id;
create or replace trigger trg_party_update
instead of update on party_v
for each row
declare
begin
--
update party
set
party_id = :new.party_id,employee_id = :new.employee_id
where
party_id = :old.party_id;
--
update party_name
set
party_id = :new.party_id,first_name = :new.first_name,last_name = :new.last_name
where
party_id = :old.party_id;
--
end;
/
您现在可以直接更新视图…
update party_v
set
employee_id = 42,last_name = 'Oxford'
where
party_id = 1;
select * from party_v;
PARTY_ID EMPLOYEE_ID FIRST_NAME LAST_NAME
1 42 Kipper Oxford
2 2000 Biff Family
3 3000 Chip Family
