Average all records in a table and update column in MYSQL

Asked

Viewed 636 times

0

I need to do a Query that passes in all table records and updates the rate_general column (this column is the average of the rate_food + rate_service + rate_price + rate_environment / 4 ) notes of each record, how to do this ? are 20 thousand records.

inserir a descrição da imagem aqui

mysql sql
  • 1

    this way: Update reviews set rate_general=(rate_food + rate_service + rate_price + rate_environment) / 4 where <critério>

  • what would be the criterion ? remembering the average of each record is different

  • 2

    if you want to update all, have no criterion. just use this query there without the where. Do the old update sem where.

  • Avoid putting images in questions, makes it difficult to answer because it is not possible to copy the code to test. A lot of people have "laziness" to analyze questions with images.

  • https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html a solution can be create this media column as calculated virtual.

3 answers

0

Add the average calculation in your update, but if your field is type integer the average will ignore the decimals, for this reason I am adding a CAST AS DECIMAL for your average field consider the decimal places.

UPDATE 
   SET rate_general = CAST((rate_food + rate_service + rate_price + rate_environment) AS DECIMAL(12,2)) / 4

Don’t forget to add the clause WHERE if you want to add some filter to update the records.

0


-1

I don’t know if it can help, but there is a function in mysql called avg, she averages her records

  • AVG is a record aggregation function (as you said). In the case of AP, it is the average using data from some columns for a record. AVG does not apply in this case.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.