CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
NightHawk198889
NightHawk198889

Reputation: 31

Update table using SELECT on combined columns

I need to update a column in on of my tables with an id that is the combination of a few characters from two other columns within the same table. Currently as I have it written it fails, because it won't allow multiple values to be return in the subquery. How can I get this to work?

Here is what I have.

UPDATE Users
SET AccountId = (
                  SELECT RIGHT(lastName, 5) + RIGHT(UserId, 6)
                  FROM Users
                )

I'm trying to end up with something like:

AccountID

lliams994521

ohnson103297

....

Upvotes: 0

Views: 52

Answers (2)

Ferhat Sayan
Ferhat Sayan

Reputation: 226

You COULD do something like this:

UPDATE Users
SET AccoundId = (SELECT RIGHT(lastName, 5) + RIGHT(UserId, 6) FROM users AS SubUsers WHERE SubUsers.UserId = MainUsers.UserId)
FROM Users MainUsers

In your case this doesn't make any sense. As @GiorgosBetsos mentioned

You don't need to do a SELECT if the UPDATE is based on fields of the same record

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You don't need to do a SELECT if the UPDATE is based on fields of the same record:

UPDATE Users
SET AccountId = RIGHT(lastName, 5) + RIGHT(UserId, 6)

Upvotes: 4

Related Questions