Using Exists in Simple Update?

From: Lee E Parsons <lparsons_at_world.std.com>
Date: 1995/07/12
Message-ID: <DBKtnv.D1J_at_world.std.com>#1/1


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
Received on Wed Jul 12 1995 - 00:00:00 CEST

Original text of this message