Update query based on join

Question

Update query based on join

kirti

Old Hand

Points: 387
More actions
August 12, 2021 at 11:25 am

#3919193

Hi,
I have 2 tables: 1 storing the amount at Quarter level:
Quarter | Amount
2020 Q1| 100 --a
2020 Q2| 250 --b
2020 Q3| 400 --c
and so on
and another table 2 which has the following structure:
Key1| GeoKey | Amount |Quarter | Percentage
1 IN 10 Q1 2020 -
2 DE 25 Q1 2020 -
3 NZ 40 Q3 2020 -
I want to update percentage column of table 2 based on table 1 value for ex:
Key1| GeoKey | Amount |Quarter | Percentage
1 IN 10 Q1 2020 - ( should be 10/a)
2 DE 25 Q1 2020 - ( should be 25/a)
3 NZ 40 Q3 2020 - (should 40/c)
Any pointers on how I can achieve this?
September 2, 2021 at 10:24 am

This was removed by the editor as SPAM
September 2, 2021 at 10:25 am

This was removed by the editor as SPAM
September 9, 2021 at 7:09 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ktflash Default port Points: 1400 More actions · Answer 1

For future posts, please provide a simple temp table code example like mine, saves the others doing the same thing every time

I left out some columns which are not relevant here.

All i did was join Table 1 and Table 2 based on Quarter. As long as the Join Column combination (here only quarter) identifies a single row in table 1, you are able to assign 1 value of Table 1 to each row in Table2

create Table #T1 (
Quarter nvarchar(2)
,Amount_T1 float
)
create Table #T2 (
Quarter nvarchar(2)
,Amount_T2 float
,Percents float
)


insert into #T1 
values
('Q1', 100.0) 
,('Q2', 250.0) 
,('Q3', 400.0) 

insert into #T2
values
('Q1', 10.0,NULL) 
,('Q1', 25.0,NULL) 
,('Q3', 40.0,NULL) 


select t1.Quarter
      ,t1.Amount_T1
  ,t2.Amount_T2
  ,t2.Amount_T2/t1.Amount_T1
from #T1 T1
     inner join #T2 T2 on t1.Quarter = t2.Quarter

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

bradley84 Grasshopper Points: 11 More actions · Answer 2

Hello,

Try this MyGiftCardSite syntax:

UPDATE Tab1, Tab2, [INNER JOIN | LEFT JOIN] Tab1 ON Tab1. C1 = Tab2. C1;

This reply was modified 3 years, 2 months ago by bradley84.