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.
Author |
Topic |
kme
Starting Member
1 Post |
Posted - 2014-04-05 : 16:54:30
|
Hello,Here are two tables:TABLE_A[ac] [dest]1 A1 12233 B3(ac=1, dest=A; ac=3, dest=B)The space between '1' and 'A' isn't showing up correctly.TABLE_B[fleet] [ron]1 A1 A1 A1 A1 A22223 B3 B(fleet=1, ron=A; fleet=3, ron=B; etc.etc.)I would like to fill TABLE_B's column "ron" by referring to TABLE_A's column "dest".ac = fleet, and for each ac, I would like to loop through the 'dest' column in TABLE_A from top to bottom to get the top most value. If there are no values (like with ac = 2), then value is blank.For 1, the value is A.For 2, the value is blank.For 3, the value is B. Therefore, in TABLE_B, for all 'ron' for fleet=1, the value is filled A.For all 'ron' for fleet=2, the value is blank.For all 'ron' for fleet=3, the value is B.How do you code this?- create variable 'v'- where ac=fleet, loop through 'dest' from top to bottom to get top-most value. variable 'v' = the value.- in TABLE_B, for each ac=fleet, insert variable 'v' into the 'ron' columnThanks so much, I would appreciate any help. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-04-13 : 14:49:53
|
looks like this to meUPDATE bSET ron = a.destFROM tableB bINNER JOIN (SELECT ac,MAX(dest) AS dest FROM tableA GROUP BY ac)aON a.ac = b.fleet ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|