代码之家 › 专栏 › 技术社区 › Atmira

基于与CTE的join更新列值

sql-server sql

Atmira · 技术社区 · 5 年前

我目前正在学习如何在SQLServer2016中使用CTE,因为我有一个很大的表,需要根据助手列的匹配更新某些列,但我不完全确定是如何完成的。我知道这可以做一个经典的更新,以及,由我想学习和理解它的CTE方式现在,因为它将在不久的将来派上用场。

State      ABR
Alabama    AL
Alaska     AK
Arizona    AZ

我需要根据State\u short和ABR之间的连接来填写State列。

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

我尝试过以下代码:

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

但这只给了我这个输出:

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

我可以看到,我丢失了一个连接,但我不知道如何使用CTE—它是在CTE中完成的,还是在下面的select子句中完成的,还是可以使用where子句完成的?基于另一个helper表的连接更新列的最佳和最简单的解决方案是什么?

2 回复 | 直到 5 年前

GMB 5 年前

首先:你并不真的需要一个CTE。您可以只使用相关子查询:

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

如果您真的想使用CTE,另一种选择是 join 在CTE中,然后更新CTE。SQLServer具有惊人的功能,可以回溯内容并将更改应用于基础表列:

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

Nick SamSmith1986 5 年前

要使查询正常工作,只需 JOIN Users 到 StateMatch 上 State_short 和 ABR 价值观:

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]

然后您可以:

SELECT *
FROM Users

输出

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