Re: Using Exists in Simple Update?

lparsons_at_world.std.com (Lee E Parsons) wrote at Wed, 12 Jul 1995 00:07:07 GMT
>
>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.
>--

I believe that the quickest way is to use ROWNUM

  update a set a.DATA =
   (select DATA from b where A.KEY = B.KEY and ROWNUM=1)

Alexandr Alesinsky
JSV Investor Received on Mon Jul 17 1995 - 00:00:00 CEST