Msg 512, Level 16

Question

Post reply

Msg 512, Level 16

deebabat

Ten Centuries

Points: 1125
More actions
May 7, 2012 at 12:28 pm

#392199

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)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Geoff A SSChampion Points: 11511 More actions · Answer 1

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.

deebabat Ten Centuries Points: 1125 More actions · Answer 2

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.

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 3

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

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

deebabat Ten Centuries Points: 1125 More actions · Answer 4

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!