使用单个查询更新多个表列值

问题描述

如何使用单个查询更新多个表中的数据?

How would you update data in multiple tables using a single query?

MySQL示例

MySQL中的等效代码:

The equivalent code in 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 = '[e-mail]',
  c.ad_postal = 'x',
  n.first_name = 'x',
  n.last_name = 'x'
WHERE
  i.confidential_dt IS NOT NULL

使用Oracle 11g的相同语句是什么?

What would be the same statement using Oracle 11g?

谢谢！

RTFM

使用Oracle时，似乎只有一个查询是不够的:

It seems a single query is insufficient when using Oracle:

http://下载-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2067717

推荐答案

/** XXX CODING HORROR... */

根据您的需求，您可以使用可更新的视图.您创建基本表的视图，并向该视图添加代替"触发器，然后直接更新该视图.

Depending on your needs, you could use an updateable view. You create a view of your base tables and add an "instead of" trigger to this view and you update the view directly.

一些示例表:

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

...然后创建一个可更新的视图

... then create an updateable view

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;
/

您现在可以直接更新视图...

You can now update the view directly...

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

这篇关于使用单个查询更新多个表列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！