Re: (Not so) Dumb sql question

} From: SDowdy@uh.edu (Steve Dowdy) } Subject: Dumb sql question } Date: 16 Nov 1994 20:24:56 GMT } Reply-To: SDowdy@uh.edu (Steve Dowdy) } Organization: University of Houston } } This is a dumb sql question on an update statement. I have two tables. } Both contain a ssn and a user id. One table may contain more than one } ssn and user id if the user has more than on user id. I don't care } which user id I update into the first table, I just want any user id } that matches the ssn. } } so } } Table 1 PI1 Table 2 IDENT } PI1_SSN IDENT_SSN } PI1_USER_ID IDENT_USER_ID } } If I use the following syntax, I get the message that the subquery has } returned more than one row. I thought that was what the "in" was for } instead of the "=". } } Here is the statement I am using. } } update PI1 set } PI1_USER_ID = (select IDENT_USER_ID from ident) } where PI1_USER_ID is null } and pi1_ssn in (select ident_ssn from ident); } } I have re-arranged the subqueries into different parts. Like I said, I } realize that I will not know which user ID I get updated into the PI1 } table. I don't care. I just wany any of the users' user ID. } } Thanks } SDowdy@uh.edu You want to use the following, or something similar: update pi1 set pi1_user_id = ( select max(ident_user_id) from ident where ident_ssn = pi1_ssn ) where pi1_user_id is null; IN syntax is indeed for situations returning more than one row. However, UPDATE requires *one value per field per row*. MAX (or MIN) will give you the one value. The embedded WHERE will give you the proper correlation. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\