May 7, 2012 at 12:28 pm
I wrote this query to update another column but I got the error: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
update client
set uniqueID = (SELECT SUBSTRING(lname,1,2)+ SUBSTRING(fname,1,2) +
replace(convert(varchar,dob,10),'-','') from client
WHERE uniqueID IS NULL)
May 7, 2012 at 12:29 pm
its because your query (SELECT SUBSTRING(lname,1,2)+ SUBSTRING(fname,1,2) +
replace(convert(varchar,dob,10),'-','') from client
WHERE uniqueID IS NULL) returns more than 1 result.
May 7, 2012 at 12:40 pm
Geoff A (5/7/2012)
its because your query (SELECT SUBSTRING(lname,1,2)+ SUBSTRING(fname,1,2) +replace(convert(varchar,dob,10),'-','') from client
WHERE uniqueID IS NULL) returns more than 1 result.
I know, how do you fix that bcos I would like to update the entire column.
May 7, 2012 at 12:53 pm
Thomas Stringer (5/7/2012)
You don't need to use a subquery.
update client
set uniqueID =
SUBSTRING(lname,1,2)+ SUBSTRING(fname,1,2) +
replace(convert(varchar,dob,10),'-','')
WHERE uniqueID IS NULL
Thank you so much, you are a problem solver not a fault finders that with no solution like others!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply