Update table a columns using columns from table b (values of 2 columns of table b need to taken from table c)

Question

I need to update table A columns col3, col4, col5 and col6 by table b columns col3, col4, col5 and col6 however table b col5 and col6 values need to come from table c col1. Means table b col5 and col6 have values in it however i need to replace them with value from table c col1 and need to update table a col5 and col6 accordingly. table a and table b has col1 and col2 in common. i am trying something like this.

Update a
a.col3 = b.col3,  
a.col4 = b.col4,
a.col5 = (select col1 from table_c c where c.col2=b.col5),
a.col6 = (select col1 from table_c c where c.col2=b.col6)
from table_A a inner join table_b
on  a.col1=b.col1 and a.col2=b.col2

can someone help me reframe above update query? thanks in advance for your help.

Could you also put sample data(what it is before the update and what it will be after the update)? — TTeeple
@TTeeple: table a abc def 0 0 -999 -999 table b abc def 1 1 -sa1283 -sa4958 table c -sa1283 1234 -sa4958 3456 After update table A should be like this table a abc def 1 1 1234 3456 hope this will be more clear – — user3375857

CynicalSection CynicalSection · Accepted Answer · 2014-03-31T13:07:26

Update a a.col3 = b.col3, a.col4 = b.col4, a.col5 = c.col1, a.col6 = d.col1 from table_A a inner join table_b on a.col1=b.col1 and a.col2=b.col2 inner join table_c c on c.col2 = b.col5 inner join table_c d on d.col2 = b.col6

Otherwise, use select top 1 in subqueries.

Update table a columns using columns from table b (values of 2 columns of table b need to taken from table c)

1 Answers