Re: Correlated updates.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/07
Message-ID: <348aaa41.459470@inet16>#1/1

On Sun, 07 Dec 1997 09:13:12 GMT, mark_at_se7en.demon.co.uk (Mark Hunter) wrote:

>I wonder if you could help me with a piece of SQL?
>
>TableA has a column called Ref, which is unique.
>TableB has columns called Oldref and Newref.
>
>For every value of TableA( Ref) there is a corresponding value in
>TableB( Oldref)
>
>I wish to update every row in TableA so that the value of Ref is
>updated to Newref (as listed in TableB).
>
>
>Suggestions please.
>Mark Hunter
>http://www.se7en.demon.co.uk

update tableA
set tableA.ref = ( select tableB.newref

                    where tableA.ref = tableB.oldref )

/

will work if in fact for each ref in tableA there is an oldref in TableB (else the ref in TableA will be updated to NULL).

If there are some refs in A that are not in B, then

update tableA
set tableA.ref = ( select tableB.newref from tableB

                    where tableA.ref = tableB.oldref )
where exists ( select NULL from tableB
                where tableA.ref = tableB.oldref )

/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Dec 07 1997 - 00:00:00 CST