In Sql, how can one change a value using a case statement?
Something like this should do it if you want to just translate group id 3 to role id 5 and keep the rest unchanged;
SELECT
users.UserName,
users.phone,
users.email,
users.status,
CASE WHEN users.group_id=3 THEN 5 ELSE users.group_id END AS RoleId
...
Tristan Descartes
Updated on June 04, 2022Comments
-
Tristan Descartes almost 2 years
Question: What is the best way to change the value in the group_id AS RoleId column so that when group_id AS RoleId = 3 the value is changed to corresponding value 5?
Background: In the following query I am comparing two tables from two different databases with no real unique key. I had to combine the first_name and last_name columns on one table alias it and then compare it with the other tables Name column. It yields the results I want, however, they have two columns that are related the RoleId column and the group_id column. They have corresponding values, so for example RoleId =5 is the same as group_id =3, but RoleId = 4 is the same as group_id = 4.
Select Statement:
SELECT users.UserName, users.phone, users.email, users.status, users.group_id AS RoleId FROM (SELECT Table1.dbo.users.first_name +' '+ Table1.dbo.users.last_name AS UserName, Table1.dbo.users.phone, Table1.dbo.users.email, Table1.dbo.users.status, Table1.dbo.users.group_id FROM Table1.dbo.users ) AS users LEFT JOIN Table2.dbo.UsersInfo ON users.UserName = Table2.dbo.UsersInfo.Name WHERE Table2.dbo.UsersInfo.Name IS NULL