Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update all row from A with values from B but top o

Author  Topic 

romeck
Starting Member

16 Posts

Posted - 2014-04-18 : 14:02:59
ID VALUE1 VALUE2
1 | a | 12
2 | v | 3
3 | c | 4
4 | g | 12


B
ID IDA VALUE
1 | 1 | 12
2 | 1 | 34
3 | 2 | 17
4 | 3 | 4
5 | 1 | 22
6 | 3 | 1



Hi there i want to update A with values from b , so the value2 from a= value from b but only with latest coresponding data based on b.id so after updtate a looks like:

ID VALUE1 VALUE2
1 | a | 22 couse max id from B with ida=1 is 5 and so the value is 22 , so A.value2=22
2 | v | 17 couse max id from B with ida=2 is 3 and so the value is 17 so A.value2=22
3 | c | 1 couse max id from B with ida=3 is 6 and so the value is 1 so A.value2=1
4 | g | 12 couse there is no ida=4 in B


TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-18 : 14:43:00
Here's one way:

update a set
a.value2 = b.value
from a
join b on b.ida = a.id
where b.id = (select max(id) from b where ida = a.id )


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -