CODEWITHSUNDEEP
mysql
Barry Connolly
Barry Connolly

Reputation: 663

mysql update one table from another

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

Upvotes: 0

Views: 68

Answers (2)

James Jithin
James Jithin

Reputation: 10565

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

Upvotes: 0

Mahendra Singh Meena
Mahendra Singh Meena

Reputation: 608

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.

Upvotes: 1

Related Questions