SQL Server - Update column from data in the same table

kgsdhlau  于 2023-05-05  发布在  SQL Server
关注(0)|答案(6)|浏览(169)

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

a0x5cqrl

a0x5cqrl1#

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
j9per5c4

j9per5c42#

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

neekobn8

neekobn83#

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
km0tfn4u

km0tfn4u4#

We can update table from self table, like this:

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

rqmkfv5c5#

There is ABSOLUTELY no need to go to all those fancy lengths suggested by the accpeted answer using INNER JOIN s orFROM s oraliases when updating from the same table - see the fiddle here !

CREATE TABLE b (x INT, y INT);

Populate:

INSERT INTO b (x) VALUES (2), (3), (4);

Check:

SELECT * FROM test;

Result:

x   y
2   null
3   null
4   null

Now - just simply:

UPDATE b SET y = x
WHERE x <= 3;

Then:

SELECT * FROM b;

Result:

x   y
2   2
3   3
4   null

Et voilà - perfectly simple! Antoine de Saint-Exupéry was right :

“La perfection est atteinte, non pas lorsqu'il n'y a plus rien à ajouter, mais lorsqu'il n'y a plus rien à retirer.”

Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.

guz6ccqo

guz6ccqo6#

If you also need to directly copy a value from one specific record to another specific record in the same table do something like this:

update DEST
set DEST.field1 = SRC.field1,
from TableName DEST
INNER JOIN TableName SRC
on DEST.ID = @DestId and SRC.ID = @SourceID

相关问题