question

harry1991 avatar image
harry1991 asked

sql joins

table1 Product | company | Demand bike | honda bike | suzuki bike | hero bike | ktm car | nissan car | renault table2 demand | company 25 | ktm 35 | davidson 59 | nissan **required output:** Product | Company | Demand bike | honda | 25 bike | suzuki | 25 bike | hero | 25 bike | ktm | 25 car | nissan | 59 car | renault | 59
joinssql queryctejoin
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sjimmo avatar image sjimmo commented ·
Can you show us code that you have already tried?
0 Likes 0 ·
harry1991 avatar image harry1991 commented ·
update a set a.demand = b.Demand from table1 inner join table2 b on a.company = b.company
0 Likes 0 ·
harry1991 avatar image harry1991 commented ·
one thing i need to know is if u join update a set a.demand = b.Demand from table1 a inner join table2 b on a.company = b.company......i know this...it will update only where it matches company in this case ktm ...so the demand will be 25 only for ktm but the problem is i need it for all the products.
0 Likes 0 ·
KenJ avatar image KenJ harry1991 commented ·
You are getting the values from table2. If there is no match, what value do you want to assign?
0 Likes 0 ·

1 Answer

·
sjimmo avatar image
sjimmo answered
Since you are using aliases, you need to alias table1. Your update statement does not know what "a" is. So change your code to: update a set a.demand = b.Demand from table1 a inner join table2 b on a.company = b.company With the shown data you will find that one row is updated, the row for ktm. In order for the other rows to be updated you will need a row for each company in table2. Otherwise remove your join and just update table1 setting demand=25
10 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

harry1991 avatar image harry1991 commented ·
but i dont have only one product bike...i have 200 different products so how to do it please help
0 Likes 0 ·
Show more comments
harry1991 avatar image harry1991 commented ·
sorry i knew that it was typo error ....but i need to do it for more items not only one
0 Likes 0 ·
Show more comments
harry1991 avatar image harry1991 commented ·
here i would like 25 to be demand in all rows related to bike (not only ktm) in table1.
0 Likes 0 ·
Show more comments
harry1991 avatar image harry1991 commented ·
should i use CTE for this to recurse the query ??
0 Likes 0 ·
KenJ avatar image KenJ commented ·
"here i would like 25 to be demand in all rows related to bike (not only ktm) in table1" If ktm were equal to 30, would you still want all bikes to be 25? if the answer is always 25, you don't need the join. Just a straight update: update table1 set demand = 25 where product = 'bike'
0 Likes 0 ·
harry1991 avatar image harry1991 commented ·
no not always 25 it should be what the value is will be may 12,2,3,34,4 anything also i have updated the question please go through it
0 Likes 0 ·
KenJ avatar image KenJ commented ·
try my answer in your other version of this question - https://ask.sqlservercentral.com/questions/138223/cte-query.html
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.