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 ·
Show more comments

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 ·
sjimmo avatar image sjimmo harry1991 commented ·
Use the following code: update a set a.demand = b.Demand from table1 a inner join table2 b on a.company = b.company As I said previously, you need to alias table1 as a. Otherwise the update doesn't know what to do.
0 Likes 0 ·
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 ·
sjimmo avatar image sjimmo harry1991 commented ·
This is going to join the 2 tables together and update all of the records in table1 which have a joining record in table2
0 Likes 0 ·
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 ·
sjimmo avatar image sjimmo harry1991 commented ·
Doing a join the way that you are trying is only going to update records which meet the join criteria. You want every record to be the same demand. Why do you want to do a join? Simply do an update of the demand field in table1
0 Likes 0 ·
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 ·
Show more comments

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.