update columns values with column of another table based on condition

SqlMysqlSql ServerOracle

Sql Problem Overview


I have two tables...

table1 ( id, item, price ) values:

id | item | price
-------------
10 | book | 20  
20 | copy | 30   
30 | pen  | 10

....table2 ( id, item, price) values:

id | item | price
-------------
10 | book | 20
20 | book | 30

Now I want to:

update table1 
   set table1.Price = table2.price 
 where table1.id = table2.id
   and table1.item = table2.item.

How do I do it?

Sql Solutions


Solution 1 - Sql

Something like this should do it :

UPDATE table1 
   SET table1.Price = table2.price 
   FROM table1  INNER JOIN  table2 ON table1.id = table2.id

You can also try this:

UPDATE table1 
   SET price=(SELECT price FROM table2 WHERE table1.id=table2.id);

Solution 2 - Sql

This will surely work:

UPDATE table1
SET table1.price=(SELECT table2.price
  FROM table2
  WHERE table2.id=table1.id AND table2.item=table1.item);

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionniceAppView Question on Stackoverflow
Solution 1 - SqlRageZView Answer on Stackoverflow
Solution 2 - SqlNadeemView Answer on Stackoverflow