oracle 使用单个查询更新多个表列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2695116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple table column values using single query
提问by Dave Jarvis
How would you update data in multiple tables using a single query?
您将如何使用单个查询更新多个表中的数据？
MySQL Example
MySQL 示例
The equivalent code in MySQL:
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 = '[email protected]', c.ad_postal = 'x', n.first_name = 'x', n.last_name = 'x' WHERE i.confidential_dt IS NOT NULL
What would be the same statement using Oracle 11g?
使用 Oracle 11g 的相同语句是什么？
Thank you!
谢谢！
RTFM
实时调频
It seems a single query is insufficient when using Oracle:
使用 Oracle 时，似乎单个查询是不够的：
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2067717
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2067717
回答by Nick Pierpoint
/** 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.
根据您的需要，您可以使用可更新视图。您创建基表的视图并向该视图添加“代替”触发器，然后直接更新视图。
Some example tables:
一些示例表：
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
回答by Irfan Mulic
I was having the same problem I couldn't find a easy way to do this in Oracle.
我遇到了同样的问题，我在 Oracle 中找不到简单的方法来做到这一点。
Look here: Oracle Update Statementsfor more info.
查看此处： Oracle 更新语句了解更多信息。
回答by Niels Wind
You could use Oracle MERGEstatement to do this. It is a bulk update-or-insert kind of statement based on joining the target table with an inline view.
您可以使用 OracleMERGE语句来执行此操作。它是一种基于将目标表与内联视图连接起来的批量更新或插入语句。
MERGE INTO bonuses D
USING (
SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80
) S ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN
UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN
INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);
if you do not need the insert part, you just omit the last 3 lines above.
如果您不需要插入部分，则只需省略上面的最后 3 行。
回答by vadipp
In some cases it's possible to use PL/SQL to achieve this. In my case I searched for matching rows in two tables by some criteria, then updated each row in a loop.
在某些情况下，可以使用 PL/SQL 来实现这一点。在我的例子中，我根据某些条件在两个表中搜索匹配的行，然后在循环中更新每一行。
Something like this:
像这样的东西：
begin
for r in (
select t1.id as t1_id, t2.id as t2_id
from t1, t2
where ...
) loop
update t1
set ...
where t1.id = r.t1_id;
update t2
set ...
where t2.id = r.t2_id;
end loop;
end;
