How to update column with the data from the columns in the same row in MYSQL?

Question

I have a table house with fields price and area, now I want to add one more column named 'average_price'.

How could I set the average_price according to the price and area in MYSQL?

Answer 1

Just one idea:

• Add your column in your favorite editor/method
• Loop through your table and define average_price value

• Update actual row with your value

  UPDATE your_table SET average_price = price/area;

Answer 2

Hello,

You can do it with a suquery. But I don't know about performance.

UPDATE `houses` AS h
SET `avg_price` = (
    SELECT AVG(`price`)
    FROM `houses` AS h2
    WHERE h2.area == h.area
)

Or you could create a temporary table in which to put the average prices and then update.

Answer 3

This doesn't sound like the kind of data you should actually store, by how often you'd have to update it - once every single time you modify the house table in any way! That's a lot of unnecessary load on the database, and a whole lot of room for human error.

Instead, I recommend acquiring it on the fly through a SQL query when you need it, and never actually storing it.

Retrieving from all areas:

SELECT `area`, AVG(`price`) AS `average_price`
FROM `house`
GROUP BY `area`
ORDER BY `average_price` ASC

Retrieving from a specific area:

SELECT `area`, AVG(`price`) AS `average_price`
FROM `house`
WHERE `area` = "New York"
GROUP BY `area`