SQL Server - 从同一个表中的数据更新列

I have a table that looks something like this:

我有一个看起来像这样的表：

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

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

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

So after the query it would look like this:

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

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

Any help greatly appreciated

任何帮助非常感谢

4 个解决方案

#1

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.

目前尚不清楚您想要使用哪个2012年值来更新哪个2013年的价值，我认为该ID应该是相同的。

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

#2

UPDATE t 
SET t.Premium = (SELECT TOP 1 t2.Premium
                 FROM dbo.TableName t2
                 WHERE t2.SetId = 2012)
FROM dbo.TableName t
WHERE t.SetId = 2013

Demonstration

示范

#3

I think this is correct solution:

我认为这是正确的解决方案：

UPDATE t 
SET t.Premium = (SELECT TOP 1 t2.Premium
                 FROM dbo.TableName t2
                 WHERE t2.SetId = 2012 AND t2.Id = t.ID)
FROM dbo.TableName t
WHERE t.SetId = 2013

#4

We can update table from self table, like this:

我们可以从自己的表更新表，如下所示：

update TABLE_A 
   set TABLE_A.Col1=B.Col2
  from TABLE_A B

#1