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
6条答案
按热度按时间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.
j9per5c42#
Demonstration
neekobn83#
I think this is correct solution:
km0tfn4u4#
We can update table from self table, like this:
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 !Populate:
Check:
Result:
Now - just simply:
Then:
Result:
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.
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: