Reputation: 249
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
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
Upvotes: 0
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
Upvotes: 2