Re: Dumb sql question
Steve Dowdy (SDowdy@uh.edu) wrote:
: 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.
: 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);
If you don't care which user_id is actually inserted and want to get
around the "subselect returned more than one row" problem, you
have to force the subselect to always return not more than one row:
update PI1 set
PI1_USER_ID = (select min(IDENT_USER_ID) from ident
where IDENT_SSN = PI1.PI1_SSN)
where PI1_USER_ID is null
This should work.
Hope it helps,
Richard
--
+----------------------------+-------------------------------------------+
| Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de |
| EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3421 |
| Klinikum Grosshadern | FAX : +49-89-7095-8886 |
| 81366 Munich, Germany | |
+----------------------------+-------------------------------------------+