Atmira
Atmira

Reputation: 249

Update Column values based on join with CTE

I'm currently learning how to use CTE in SQL Server 2016, as I have some large table where I need to update some columns based on a match from a helper column, and I'm not completely sure how it's done. I know this can be done with a classic update as well, by I want to learn and understand it the CTE way right now, as it will come handy in a near future.

I have this Helper table:

State      ABR
Alabama    AL
Alaska     AK
Arizona    AZ

And I need to fill out the column State based on a join between state_short and ABR.

User    State_short State
John    AL          NULL
Carl    AK          NULL
Ivan    AZ          NULL
Martin  AZ          NULL
William AK          NULL
Sean    AL          NULL
Bob     AL          NULL

I Have tried this code:

WITH StateMatch AS 
(
SELECT [State] AS StateName, [ABR] FROM [States]
)
UPDATE [Users]
SET [State] = StateName
FROM StateMatch

But this does only give me this output:

User    State_short State
John    AL   Alabama
Carl    AK   Alabama
Ivan    AZ   Alabama
Martin  AZ   Alabama
William AK   Alabama
Sean    AL   Alabama
Bob     AL   Alabama

I can see, that I am missing a join, but I'm not sure how to with CTE - Is it done within the CTE, or in the following select clause or can it be done with a where clause? What is best and most simple solution to updating a column based on a join from another helper-table?

Upvotes: 0

Views: 3339

Answers (2)

GMB
GMB

Reputation: 222582

To start with: you don't really need a CTE for this. You could just use a correlated subquery:

update Users
set State = (
    select s.State from States s where s.ABR = Users.State_short
)
where State is null

If you really want to use a CTE, an alternative option is to join within the CTE, and then update the CTE. SQLServer has amazing capabilities to backtrack things and apply the changes to the underlying table column:

with cte as (
    select u.State, s.State New_state
    from Users u
    inner join States s on s.ABR = u.State_short
)
update cte set State = New_state

Demo on DB Fiddle

Upvotes: 0

Nick
Nick

Reputation: 147206

To make your query work, you just need to JOIN Users to StateMatch on the State_short and ABR values:

WITH StateMatch AS 
(
SELECT [State] AS StateName, [ABR] FROM [States]
)
UPDATE [Users]
SET [State] = StateName
FROM [Users] u
JOIN [StateMatch] s ON u.[State_short] = s.[ABR]

You can then:

SELECT *
FROM Users

Output

User        State_short     State
John        AL              Alabama
Carl        AK              Alaska
Ivan        AZ              Arizona
Martin      AZ              Arizona
William     AK              Alaska
Sean        AL              Alabama
Bob         AL              Alabama

Demo on SQLFiddle

Upvotes: 2

Related Questions