Inline query to update multiple rows

Question

Here is a brief description of the tables I'm working with in Oracle 10g:

Notes: Table : jnldetail : Single row with data as shown. There are multiple package id's attached to the same bill_ref_no for an account. Therefore, I'm trying to update "jnldetail " with the multiple package_id's.

Relation between index_bill_ref and bill_ref_no : 1 - 1 Relation between account_no and ( index_bill_ref and bill_ref_no ) : 1 - Many

Table : jnldetail : account_no bill_ref_no amount

8594822 74282843 822

I'm adding another column package_id with the following command:

alter table jnldetail add package_id number(10)

table: bill_invoice:

account_no bill_ref_no index_bill_ref

8594822 74282843 763653495

table: bill_invoice_detail:

index_bill_ref package_id component_id

763653495 20000077 20000177

763653495 20000250 20000528

763653495 13000019 13000137

Expected Result: Table : jnldetail :

account_no bill_ref_no amount package_id

8594822 74282843 822 20000077

8594822 74282843 822 20000250

8594822 74282843 822 13000019

My Query is:

UPDATE jnldetail tp
SET tp.package_id = (select t1.package_id from bill_invoice_detail t1, bill_invoice t2
where t1.index_bill_ref = t2.index_bill_ref and
t2.account_no = tp.account_no)

The error message is : ora 01427 : single row subquery returns more than one row

Any inputs will be helpful.

Thanks!

Answer 1

The problem is that you're trying to set tp.package_id to more than one number, because your subquery is returning more than one result, e.g. 20000077 and 13000019. You'll need to alter the subquery so that only one value is returned.

Answer 2

Why not keep the tables separate and use a join when you are ready to get the complete data?

Answer 3

This is tricky for two reasons:

1) you want to update the existing row, and want to add two new rows

2) the two new rows need the data from both the original jnldetail table (amount) and the bill_invoice tables (package_id)

To address 1, you can use the MERGE statement, but because of 2, the jnldetail is needed in the using clause of the MERGE statement.

Here is your example:

SQL> create table jnldetail (account_no, bill_ref_no, amount)
  2  as
  3  select 8594822, 74282843, 822 from dual
  4  /

Tabel is aangemaakt.

SQL> alter table jnldetail add package_id number(10)
  2  /

Tabel is gewijzigd.

SQL> create table bill_invoice (account_no, bill_ref_no, index_bill_ref)
  2  as
  3  select 8594822, 74282843, 763653495 from dual
  4  /

Tabel is aangemaakt.

SQL> create table bill_invoice_detail (index_bill_ref, package_id, component_id)
  2  as
  3  select 763653495, 20000077, 20000177 from dual union all
  4  select 763653495, 20000250, 20000528 from dual union all
  5  select 763653495, 13000019, 13000137 from dual
  6  /

Tabel is aangemaakt.

The tables as you described them.

SQL> UPDATE jnldetail tp
  2     SET tp.package_id =
  3         ( select t1.package_id
  4             from bill_invoice_detail t1
  5                , bill_invoice t2
  6            where t1.index_bill_ref = t2.index_bill_ref
  7              and t2.account_no = tp.account_no
  8         )
  9  /
       ( select t1.package_id
         *
FOUT in regel 3:
.ORA-01427: single-row subquery returns more than one row

Your update statement fails, because you try to assign the result of a 3-rows-returning-query to a single column.

Here is the MERGE statement:

SQL> merge into jnldetail jd
  2  using ( select bi.account_no
  3               , bi.bill_ref_no
  4               , jd.amount
  5               , bid.package_id
  6               , row_number() over (partition by bi.account_no,bi.bill_ref_no,bi.index_bill_ref order by null) rn
  7            from bill_invoice bi
  8               , bill_invoice_detail bid
  9               , jnldetail jd
 10           where bi.index_bill_ref = bid.index_bill_ref
 11             and bi.account_no = jd.account_no
 12             and bi.bill_ref_no = jd.bill_ref_no
 13        ) bi
 14     on (   jd.account_no     = bi.account_no
 15        and jd.bill_ref_no    = bi.bill_ref_no
 16        and bi.rn             = 1
 17        )
 18   when matched then
 19        update set package_id = package_id
 20   when not matched then
 21        insert values (bi.account_no,bi.bill_ref_no,bi.amount,bi.package_id)
 22  /

3 rijen zijn samengevoegd.

Note that we pick an arbitrary row to be updated: the one with rn = 1. It leads to the desired result set:

SQL> select * from jnldetail
  2  /

ACCOUNT_NO BILL_REF_NO     AMOUNT PACKAGE_ID
---------- ----------- ---------- ----------
   8594822    74282843        822   13000019
   8594822    74282843        822   20000077
   8594822    74282843        822   20000250

3 rijen zijn geselecteerd.

Regards, Rob.