SQL Server - 从同一个表中的数据更新列 [英] SQL Server - Update column from data in the same table

问题描述

我有一张看起来像这样的桌子:

I have a table that looks something like this:

SetId      ID       Premium
2012        5          Y
2012        6          Y
2013        5          N
2013        6          N

我想用 setid 等于 2012 的溢价值更新 2013 年的记录.

I want to update the 2013 records with the premium values where the setid equals 2012.

所以在查询之后它看起来像这样:

So after the query it would look like this:

SetId      ID       Premium
2012        5          Y
2012        6          Y
2013        5          Y
2013        6          Y

非常感谢任何帮助

推荐答案

不清楚您想使用哪个 2012 值来更新哪个 2013 值，我假设 ID 应该是相同的.

It's not clear which 2012 value you want to use to update which 2013 value, i've assumed that the ID should be the same.

使用表变量的完整示例，您可以在管理工作室中自行测试.

Full example using table variables that you can test yourself in management studio.

DECLARE @Tbl TABLE (
    SetId INT,
    Id INT, 
    Premium VARCHAR(1)
)

INSERT INTO @Tbl VALUES (2012, 5, 'Y')
INSERT INTO @Tbl VALUES (2012, 6, 'Y')
INSERT INTO @Tbl VALUES (2013, 5, 'N')
INSERT INTO @Tbl VALUES (2013, 6, 'N')

--Before Update
SELECT * FROM @Tbl 

--Something like this is what you need
UPDATE t 
SET t.Premium = t2.Premium 
FROM @Tbl t 
INNER JOIN @Tbl t2 ON t.Id = t2.Id 
WHERE t2.SetId = 2012 AND t.SetId = 2013

--After Update    
SELECT * FROM @Tbl 

这篇关于SQL Server - 从同一个表中的数据更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！