Re: Using Exists in Simple Update?

lparsons_at_world.std.com (Lee E Parsons) wrote:

>I have been working on a test case involving the following table structures
>and data.
 

> Table A (Key CHAR, data number)
> A NULL
> B NULL
> C NULL
 
> Table B (Key CHAR, data number)
> A 1
> B 2
> C 3
> A 1
> B 2
> C 3
 

>I'm trying to find the quickest way to set A.NUMBER = B.NUMBER. I can trust
>that for all cases of KEY, DATA will always be the same.
 

>I can of course do
 

> update a set a.DATA =
> (select max(DATA) from b where A.KEY = B.KEY)
 

>But the use of the max looks a bit off. Is there a way to form this update
>so that only the first occurence of KEY need be read from file B? I have
>played with the WHERE EXISTS clause but can't seem to get it worked out.
>--
>Regards,
 

>Lee E. Parsons
>Systems Oracle DBA lparsons_at_world.std.com

If you don't care which of the data values from B are used to update A then the following will work:

 1 update a
 2* set data = ( select data from b where b.k = a.k and rownum = 1 );

The select max(data) from b where .... is 'better' because it always produces reproducible results. In theory, the update I provided could return a different value from B for the data column in A if run multiple times. If the value of data in B for a given value of the KEY column is always the same, then the update with rownum is as 'safe' as the max query but faster.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Wed Jul 12 1995 - 00:00:00 CEST