mysql update one table from another

Question

I have a massive database over over 2.7 million rows. It contains data on uk property prices.

The first Table is called PricePaid And has a column called Price and Loc4.

Now I am trying to get the average for each year grouped by loc4 and update another tabel called PricePaidByCounty.

I have created this SQL statement :

INSERT PricePaidByCounty (County, Avg2013)
SELECT Loc4,
Avg(Price) as AvgPrice2013 FROM PricePaid WHERE Date Like '%2013%'
Group BY Loc4

This works fine for inserting initial row but I want to use an update statement instead as I will need to run this SQL query each month.

Can anyone show me how to change this Insert into an update.

I am doing this as I need to quickly display average house price for each location by year. And the Database is that big I dont want to do this on the flu

Thanks

You can use JOIN and subselects inside your update-statements — frlan, Sep 21 '15 at 09:22
this database schema is assumed to be poorly designed is all I can say. The fact that you have a column called Avg2013, and doing a `like` — Drew, Sep 21 '15 at 09:36
the way to do it is to have a Junction table, and in it have avg information by year. and a `composite` on a generic column named something like `theYear` (along with another column). See my answer [here](http://stackoverflow.com/a/32620163/1816093). He wanted to do data as CSV in one column. You want to add columns per year (same difference bad design) — Drew, Sep 21 '15 at 09:44
As such you have fast access via joins and index use, versus using a `like` which is a no no — Drew, Sep 21 '15 at 09:47

score 1 · Answer 1 · edited Sep 21 '15 at 09:30

You can update your table using following query using insert ... on duplicate key update statement (https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html):

INSERT INTO PricePaidByCounty (County, Avg2013)
SELECT Loc4, Avg(Price) as AvgPrice2013 
FROM PricePaid 
WHERE Date Like '%2013%' 
GROUP BY Loc4
ON DUPLICATE KEY UPDATE Avg2013=AvgPrice2013

For this to work you need to make sure that a set of (County, Avg2013) is defined as unique key.

James Jithin · Answer 2 · 2015-09-22T02:40:56.950

Looks like table PricePaidByCounty needs three columns Country, Year and Average so that while updating by year you can choose to update by particular year. Then the query would be like:

UPDATE PricePaidByCounty ppbc JOIN (SELECT Loc4,
Avg(Price) as AvgPrice FROM PricePaid WHERE Date Like '%2013%'
Group BY Loc4) ap SET ppbc.Average = ap.AvgPrice WHERE ppbc.Country = ap.Loc4 AND ppbc.Year = 2013;

This can be enhanced to group by Loc4 and Year and directly update to PricePaidByCounty. It may run for days as you have multiple records. :D

mysql update one table from another

2 Answers2