Re: Dumb sql question
In article <3adpqo$de0@masala.cc.uh.edu>, SDowdy@uh.edu (Steve Dowdy) says:
>
>
>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.
update PI1
set PI1_USER_ID = (select IDENT_USER_ID
from ident
where IDENT_SSN = PI1_SSN
)
where PI1_USER_ID is null;
Is this what you want ?
########################################################
# ____ ____ ____ #
# | _ | | | | | Michael Lackowski #
# | (_)| | | | | #
# | ___| _| _|__| _| IVU GmbH #
# |( (_ \\ ( (_ | (_ | Bundesallee 129 #
# | | | \\ \\/ /| | | | D-12161 Berlin #
# | | | \\ /|| |__| | #
# |(____) |\\_/ |\\________) phone : +49 30 850006-37 #
# | | | | | | fax : +49 30 850006-66 #
# |____| |____| |____| e-mail: ml@ivu-berlin.de #
# #
########################################################