Oracle update statement with group function

Question

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

Answer 1

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.

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...

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.

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;

Which gives you:

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           

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.

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.

It's simpler to do a 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.

Your table now has:

select * from branch_cp;

AREA  BRANCH_CODE BRANCH_NAME
----- ----------- -----------
01    04          A           
01    05          B           
03    06          C           

SQL Fiddle.