CODEWITHSUNDEEP
phpmysqlsqlsql-updatesql-insert
Daniel
Daniel

Reputation: 3

How should I update a MySQL table column with the average of other two columns?

I have a table with the following columns: id (AI, PK), name, surname, group, grade1, grade2, average and I must insert from a PHP script the average value of 'grade1' and 'grade2' in the 'average' column.

I have used:

INSERT INTO `table_name`(`average`) SELECT AVG((`grade1` + `grade2`)/2.0) FROM table_name GROUP BY `id`

I have the grades inserted, but somehow when I run the SQL statement, it creates new rows only with the average values. I need to UPDATE the column 'average' in the existing rows, not create new ones.

Thank you in advance!

Upvotes: 0

Views: 480

Answers (2)

puntolino
puntolino

Reputation: 56

You should use UPDATE ... WHERE ... instead of INSERT INTO ...

I would also consider to use a trigger instead of using PHP.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You seem to want update, not insert:

update table_name
    set average = (grade1 + grade2) / 2;

I should note that having such a calculated column is dangerous, because it can get out-of-synch. Much safer are generated columns, which you can express as:

alter table table_name add column average decimal(10, 2)
    generated always as ( (grade1 + grade2) / 2);

This is calculated when the value is queried so it is always up-to-date.

Upvotes: 2

Related Questions