CODEWITHSUNDEEP
sqlsql-server
user1431633
user1431633

Reputation: 658

UPDATE a column with a subquery

I have a a user table (tbUser) with a column emailaddress and username and I'm trying to get rid of any email address in the username column. The below query finds all records where the person used an '@' in their username and I want to just take everything to the left of the '@' -- this is good enough for me to figure out if it's an email address don't need to be too strict whether it's actually an email address.

SELECT LEFT(username, CHARINDEX('@', username) - 1) AS Expr1
FROM     tbUser
WHERE  (username LIKE '%@%')

Now I want to UPDATE all usernames so that it's only the text to the left of the '@' but not sure how to do it. It's something like the below query but it's not quite right.

UPDATE tbUser
SET username = (SELECT LEFT(username, CHARINDEX('@', username) - 1) AS Expr1
                FROM tbUser AS tbUser_1)
WHERE  (username LIKE '%@%')

Upvotes: 1

Views: 45

Answers (1)

Filipe Roxo
Filipe Roxo

Reputation: 722

If that SELECT returns exactly what you want you should be able to update the table just as:

UPDATE tbUser
SET username = LEFT(username, CHARINDEX('@', username) - 1)
WHERE username LIKE '%@%'

Upvotes: 2

Related Questions