update columns values with column of another table based on condition

Question

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?

Solution 1

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

This will surely work:

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

Comments

[niceApp]

It gives me error message: invalid object table1.

[niceApp]

It gives me error The multi-part identifier "table1.price" could not be bound.

[RageZ]

make sure you don't use Table1 but table1 MSSQL seems to be case sensitive.

[Kanagavelu Sugumar]

But this is updating more no of rows :( than it is...

[Kanagavelu Sugumar]

It should update only the no of rows returned within the inner query, but doubled the no of update is happening :(

[Sam]

It working fine for me but if I'm using aliasing it's not working in SQL Server 2008 like update table1 set a.Price = b.price FROM table1 a INNER JOIN table2 b on a.id = b.id Error: The multi-part identifier "a.Price" could not be bound

[rjmd]

@Sam, that is because you are referencing table1 in the UPDATE and FROM clauses (the same table is referenced twice). According to the script you are actually updating the field in the table you are querying rather than the one you are updating. The SET clause should reference the UPDATE table i.e. UPDATE table1 SET price = b.price FROM (SELECT id, price AS p FROM table1) a INNER JOIN table2 b on a.id = b.id

[cikatomo]

it's not working in mysql

[Zimano]

Not working in Access; gives missing operator error.

[rajthakur]

For mysql: UPDATE table1 INNER JOIN table2 ON table1.id = table2.id SET table1.Price = table2.price can be used

[Himanshu Bansal]

Updating double...

[vijay]

This should be the accepted answer because most of the DBs support subqueries, instead of joins, for an UPDATE statement. @niceApp

[inquisitive]

you should not give table1.Price after SET, simply give column name Price. The example would work in postgres. UPDATE table1 SET Price = table2.price FROM table1 INNER JOIN table2 ON table1.id = table2.id