CODEWITHSUNDEEP
sqlsql-serversql-server-2012
SP1
SP1

Reputation: 1202

Updating column by getting data from another table

I have a table shown below

Code APAC APACRatio EMEA EMEARatio
SL   2               4
PQ   5               5

I have another table 

Code Count
SL    3
PQ    4

I have to update the first table based on code so I can get the below results

Code APAC APACRatio EMEA EMEARatio
 SL   2     2/3       4   4/3
 PQ   5     5/4       10  10/4

So I want to calculate the ratio by joining on the code and then updating it. I can do it column by column manually but not sure if there is a way to do it dynamically.

Upvotes: 0

Views: 39

Answers (1)

S3S
S3S

Reputation: 25112

Straight forward join with division

select 
t1.Code
,t1.[APAC APACRatio] / t2.[Count]
,t1.[EMA EMEARatio] / t2.[Count]
from table 1 t1
inner join table2 t2 on t1.Code = t2.Code

Or, maybe concatenation?

select 
t1.Code
,concat(t1.[APAC APACRatio],'/',t2.[Count])
,concat(t1.[EMA EMEARatio],'/',t2.[Count])
from table 1 t1
inner join table2 t2 on t1.Code = t2.Code

Thus, 

update t1
    set 
     t1.[APAC APACRatio] =  concat(t1.[APAC APACRatio],'/',t2.[Count])
    ,t1.[EMA EMEARatio] = concat(t1.[EMA EMEARatio],'/',t2.[Count])
    from table1 t1
    inner join table2 t2 on t1.Code = t2.Code

Upvotes: 2

Related Questions