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?

@mrp: you shouldn't put every RDBM as tag because, someone might advice you some vendor specific solution. — RageZ, Nov 17 '09 at 02:02
For PostgreSQL, see [SO answer](https://stackoverflow.com/a/24789509/8285811) — Akaisteph7, May 31 '22 at 22:03

score 305 · Accepted Answer · edited Jun 17 '19 at 22:27

305

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);

edited Jun 17 '19 at 22:27

Frost10000

21
8

answered Nov 17 '09 at 02:04

RageZ

26,800
12
67
76

4

It gives me error message: invalid object table1. – niceApp Nov 17 '09 at 03:17
4

It gives me error The multi-part identifier "table1.price" could not be bound. – niceApp Nov 17 '09 at 03:23
make sure you don't use `Table1` but `table1` MSSQL seems to be case sensitive. – RageZ Nov 17 '09 at 03:41
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 – Sam Feb 27 '15 at 13:07
1

@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` – rjmd Jun 16 '15 at 08:38
11

it's not working in mysql – cikatomo Sep 30 '15 at 19:30
1

Not working in Access; gives missing operator error. – Zimano Apr 05 '16 at 17:17
36

For mysql: `UPDATE table1 INNER JOIN table2 ON table1.id = table2.id SET table1.Price = table2.price` can be used – rajthakur Mar 23 '17 at 07:23
1

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` – inquisitive May 02 '18 at 06:27

score 99 · Answer 2 · edited Mar 06 '13 at 21:39

99

This will surely work:

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

edited Mar 06 '13 at 21:39

Alexander

23,432
11
63
73

answered Nov 17 '09 at 12:19

Nadeem

999
5
2

1

But this is updating more no of rows :( than it is... – Kanagavelu Sugumar Jan 15 '14 at 12:32
1

It should update only the no of rows returned within the inner query, but doubled the no of update is happening :( – Kanagavelu Sugumar Jan 15 '14 at 12:37
Updating double... – Himanshu Bansal Mar 30 '17 at 06:20
This should be the accepted answer because most of the DBs support subqueries, instead of joins, for an UPDATE statement. @niceApp – vijay Mar 08 '18 at 13:43

update columns values with column of another table based on condition

2 Answers2

Linked