SQL Server - Update column from data in the same table

Question

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.

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

Answer 1

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

Answer 2

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