CODEWITHSUNDEEP
sqlmysql
Jichao
Jichao

Reputation: 41805

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

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?

Upvotes: 1

Views: 291

Answers (3)

doppelgreener
doppelgreener

Reputation: 5094

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`

Upvotes: 1

fabrik
fabrik

Reputation: 14365

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;

Upvotes: 2

Alin P.
Alin P.

Reputation: 44346

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.

Upvotes: 1

Related Questions