带组功能的 Oracle 更新语句 [英] Oracle update statement with group function
问题描述
我对带有组函数的 Oracle Update 语句有一些问题我想更新另一个表中的所有记录.但它显示 SQL 错误:ORA-01427:单行子查询返回多于一行这是我的例子.
I have some problem with Oracle Update statement with group function I want to update a column all records from another table. But it shows SQL Error: ORA-01427: single- row subquery returns more than one row Here is my example .
create table branch_cp
(
area varchar2 (5) ,
branch_code varchar2 (5) primary key,
branch_name varchar2 (20));
------
create table branch_cp_2
(
area varchar2 (5) ,
branch_code varchar2 (5) primary key,
branch_name varchar2 (20));
----------------------------------
insert into branch_cp values ('01', '01', 'A');
insert into branch_cp values ('01', '02', 'B');
insert into branch_cp values ('03', '03', 'C');
----------
insert into branch_cp_2 values ('01', '04', 'D');
insert into branch_cp_2 values ('01', '05', 'E');
insert into branch_cp_2 values ('03', '06', 'F');
----------------------
select * from branch_cp;
------------------
update branch_cp
set branch_cp.branch_code = (select branch_cp_2.branch_code
from branch_cp_2
where branch_cp.area =branch_cp_2.area)
where exists (select 1 from branch_cp_2 where branch_cp_2.area = branch_cp.area) ;
如何在更新语句中使用组函数.请帮忙
How to use Group Function in update statement. Please Help
推荐答案
你每个表中有两条记录，其中area为01，你需要将它们设置为满足主键的不同值 - 您不能将它们都设置为第二个表中的最大值或最小值，因此您真的不想进行任何分组.
You have two records in each table where area is 01, and you need to set them to different values to satisfy the primary key - you can't set them both to the max or min value from the second table, so you don't really want to do any grouping.
在具有相同 area 的记录之间似乎没有任何其他排序，所以我假设它是任意的，并且每个 area 从另一个表中获取哪个 branch_code.如果它不是任意的，则需要指定规则...
There doesn't seem to be any other ordering between records with the same area, so I'll assume it's arbitrary and it doesn't matter which record for each area gets which branch_code from the other table. If it isn't arbitrary then the rules would need to be specified...
如果您需要匹配一组记录中的任意顺序，则相关更新很棘手.您需要某种方法来识别行顺序，但是将 row_number() 列添加到原始表以创建内联视图将导致 ORA-01732 错误.
A correlated update is tricky if you need to match on an arbitrary order within a group of records. You need some way to identify the row order, but adding a row_number() column to the original tables to create an inline view will lead to an ORA-01732 error.
不过，您可以使用目标表的 rowid 伪列；您只需要在相关性中进行额外的连接即可获得相同的值以及新的 branch_code.类似的东西:
You can, though, use the target table's rowid pseudocolumn; you just have to do an additional join in the correlation to get that same value along with the new branch_code. Something like:
select bc.rid,
bc.area,
bc.branch_code,
bc.branch_name,
bc2.area,
bc2.branch_code,
bc2.branch_name
from (
select bc.*,
bc.rowid as rid,
row_number() over (partition by bc.area order by bc.branch_code) as rn
from branch_cp bc
) bc
join (
select bc2.*,
row_number() over (partition by bc2.area order by bc2.branch_code) as rn
from branch_cp_2 bc2
) bc2
on bc2.area = bc.area
and bc2.rn = bc.rn;
这给了你:
RID AREA BRANCH_CODE BRANCH_NAME AREA BRANCH_CODE BRANCH_NAME
------------------ ----- ----------- ----------- ----- ----------- -----------
AAAwy+AAEAAAA0DAAA 01 01 A 01 04 D
AAAwy+AAEAAAA0DAAB 01 02 B 01 05 E
AAAwy+AAEAAAA0DAAC 03 03 C 03 06 F
现在您实际上并不需要所有这些列，您只需要 rid(branch_cp.rowid)和相关的 branch_cp_2.branch_code.
Now you don't actually need all those columns, you only need the rid (the branch_cp.rowid) and the correlated branch_cp_2.branch_code.
但您也只想在匹配时进行更新 - 以使其他表中没有值的任何行无效 - 因此您必须在 exists 子查询中重复该连接.
But you also only want to update when there is a match - to void nulling any rows where there is no value in the other table - so you would have to repeat that join in the exists subquery.
merge 更简单:
merge into branch_cp bc
using (
select bc.rid,
bc2.branch_code
from (
select bc.*,
bc.rowid as rid,
row_number() over (partition by bc.area order by bc.branch_code) as rn
from branch_cp bc
) bc
join (
select bc2.*,
row_number() over (partition by bc2.area order by bc2.branch_code) as rn
from branch_cp_2 bc2
) bc2
on bc2.area = bc.area
and bc2.rn = bc.rn
) bc2
on (bc.rowid = bc2.rid)
when matched then update set bc.branch_code = bc2.branch_code;
3 rows merged.
您的桌子现在有:
select * from branch_cp;
AREA BRANCH_CODE BRANCH_NAME
----- ----------- -----------
01 04 A
01 05 B
03 06 C
这篇关于带组功能的 Oracle 更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
