CODEWITHSUNDEEP
sqlsql-server-2008switch-statementcase
Tristan Descartes
Tristan Descartes

Reputation: 145

In Sql, how can one change a value using a case statement?

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

Upvotes: 2

Views: 18161

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

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
...

Upvotes: 3

Related Questions