update from select

[sammybee]

Hi All,

If your doing an update where = to select from etc, if the table has outer joins does this cause problems? the code I have written doesn't seem to work and If I restrict to 1 record, it updates all records with the same 1 row instead of only updating one record. Any ideas where I'm going wrong?

Many thanks Sam

 

[lewisp]

It would assist us if you post your UPDATE code, so that we can help you.

Outer joins in a sub-query should make no difference.

 

[sammybee]

Hey,

Below is my code, many thanks Sam


update sb_prop_accounts

set sb_lh_par_forename = (SELECT p.par_per_forename
FROM sb_prop_accounts,
(SELECT scip_rac_accno, MAX (scip_par_refno) par_refno, MAX (ROWID)
FROM sc_invoice_parties
GROUP BY scip_rac_accno) scip,
address_usages,
addresses,
parties p
WHERE sb_pay_ref = scip.scip_rac_accno(+)
AND scip.par_refno = aus_par_refno(+)
AND aus_end_date IS NULL
AND aus_aut_far_code = 'CONTACT'
AND aus_adr_refno = adr_refno(+)
AND aus_par_refno = p.par_refno(+))

 

[lewisp]

You have a problem because you have no WHERE clause on the UPDATE. What you are asking for in your code is to update EVERY row with the result from your SELECT.

You need something along the lines of

UPDATE sb_prop_accounts
SET    <your_column> = 'something'
WHERE  sb_lh_par_forename IN
         (SELECT  p.par_per_forename
          FROM sb_prop_accounts,
               (SELECT cip_rac_accno,
                       MAX (scip_par_refno) par_refno,
                       MAX (ROWID)
                FROM sc_invoice_parties
                GROUP BY scip_rac_accno) scip,
               address_usages,
               addresses,
               parties p
          WHERE sb_pay_ref = scip.scip_rac_accno(+)
          AND   scip.par_refno = aus_par_refno(+)
          AND   aus_end_date IS NULL
          AND   aus_aut_far_code = 'CONTACT'
          AND aus_adr_refno = adr_refno(+)
          AND aus_par_refno = p.par_refno(+))

BTW, what is your thinking behind selecting the MAX(ROWID)?

 

[sammybee]

There may be more than one par_refno per rac_accno in the select, so max row_id ensure's I only get one row returned.

Many thanks for your help, I'll give it a go now!

Cheers