This is my table:

I want to get the max total in a particular subject and store it in another column whenever the total column is inserted or updated.

This is what I want the table to look like:

I have tried this query but it doesn't add the max scores in each subject against all the student_id.

SELECT MAX(`total`) AS highest 
FROM results 
GROUP BY student_id

I suppose I should use a trigger for this but the normal query is also okay by me.

CodePudding user response：

If your column does not yet exist in your table, you need to add it with an ALTER statement.

ALTER TABLE <your_table_name> ADD max INT;

Then you can first select the "max" value for each "subject_id" using an aggregation, then use it inside the UPDATE statement exploiting a JOIN operation:

UPDATE     <your_table_name>
INNER JOIN (SELECT subject_id, 
                   MAX(total) AS total_max
            FROM <your_table_name>
            GROUP BY subject_id) cte
        ON <your_table_name>.subject_id = cte.subject_id
SET <your_table_name>.max = cte.total_max;

Check the demo here.

CodePudding user response：

Assuming you are using MySQL 8 .

Ideally, instead of storing MaxTotal data into each column, you should get such the data while selecting like this:

WITH CTE AS (SELECT subject_id,MAX(total) AS MaxTotal
FROM results
GROUP BY subject_id
)
SELECT results.*,CTE.MaxTotal
FROM results
JOIN CTE ON results.subject_id = CTE.subject_id;

However, if you still need to update it anyway, use UPDATE with JOIN

WITH CTE AS (SELECT subject_id,MAX(total) AS MaxTotal
FROM results
GROUP BY subject_id
)
UPDATE results 
JOIN CTE ON results.subject_id = CTE.subject_id
SET results.MaxTotal = CTE.MaxTotal;

Output after the update:

See this db<>fiddle.