MySQL update details in table 1 from table 2 where ID matches

Question

I have had a look through other solutions posted on here for similar questions but cant find one that works.

I'm updating prices in a table which has 70k+ products, I have imported the new prices into a new table to update them from

Table 1: Products
Product, Description, Price, Updated

Table 2: Prices_2015
Product, Description, Price

Product is the unique identifier, a product code, the price and description in table 1 need updated with that from table 2, and the Updated field in table 1 needs to have a UNIX timestamp for just now added.

Any help is appreciated!

possible duplicate of [Advanced MySql Query: Update table with info from another table](http://stackoverflow.com/questions/1202075/advanced-mysql-query-update-table-with-info-from-another-table) — Gunaseelan, Jun 01 '15 at 13:56

score 2 · Accepted Answer · answered Jun 01 '15 at 13:50

2

You can do it easily, by calling two tables to UPDATE, but actually only updating the fields in one of them:

UPDATE Products p, Prices_2015 pr
SET p.Price = pr.Price, p.Description = pr.Description, p.Updated = NOW()
WHERE p.Product = pr.Product

answered Jun 01 '15 at 13:50

Tibor B.

1,680
1
10
12

score 0 · Answer 2 · answered Jun 01 '15 at 13:43

0

UPDATE `Products` a
JOIN `Prices_2015` b ON `a`.`Product` = `b`.`Product`
SET `a`.`Price` = `b`.`Price`
   ,`a`.`Description` = `b`.`Description`
   ,`a`.`Updated` = NOW();

answered Jun 01 '15 at 13:43

ragerory

1,360
1
8
27

Thanks, would it be normal for this to take some time to run? ran the query few mins ago and chrome still has the loading spinner going – Callum Jun 01 '15 at 13:50
Well, you are joining all 70k rows in two separate tables, and then updating all of them in one of the tables. So, it doesn't surprise me that it will take a little bit. You can make it more efficient by adding a `WHERE` clause and only update the ones you care about. For instance, `WHERE ``a``.``Price`` <> ``b``.``Price` or something to that effect. Are you doing this through a web page (you said Chrome) -- look in MySql Workbench to see the performance details. – ragerory Jun 01 '15 at 13:56
Hey! yes it did work but I had to run a new import, and found a different answer ran it more efficiently – Callum Jun 12 '15 at 11:56

score 0 · Answer 3 · answered Jun 01 '15 at 13:58

As per your query problem you want update a products table on base of prices_2015. which both table have product as unique.

UPDATE Products
INNER JOIN Prices_2015 ON (Products.Product = Prices_2015.Product)
SET Products.Price = Prices_2015.Price

Hope this will help.

MySQL update details in table 1 from table 2 where ID matches

3 Answers3