SQL Server - Update column from data in the same table
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
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
We can update table from self table, like this:
update TABLE_A
set TABLE_A.Col1=B.Col2
from TABLE_A B
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