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

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

Can you show us code that you have already tried?
0 Likes 0 ·
update a set a.demand = b.Demand from table1 inner join table2 b on a.company = b.company
0 Likes 0 ·
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 ·
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

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

but i dont have only one product bike...i have 200 different products so how to do it please help
0 Likes 0 ·
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 ·
sorry i knew that it was typo error ....but i need to do it for more items not only one
0 Likes 0 ·
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 ·
here i would like 25 to be demand in all rows related to bike (not only ktm) in table1.
0 Likes 0 ·
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 ·
should i use CTE for this to recurse the query ??
0 Likes 0 ·
"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.