MYSQL UPDATE TABLE ERRORS

Question

I want do some calculations for the column total by adding the two marks columns for the specific std_id.

For example 30+25=55, total=55 for std_id 32

+--------+-----------+-------+----+----------------+-------+    
| std_id | course_id | marks | mp | subject        | total |    
+--------+-----------+-------+----+----------------+-------+    
|     32 |        40 | 30.00 | 22 | Elective Maths |     0 |    
|     32 |        40 | 25.00 | 12 | Elective Maths |     0 |    
|     39 |        40 | 30.00 | 22 | Elective Maths |     0 |    
|     39 |        40 | 20.00 | 12 | Elective Maths |     0 |   
+--------+-----------+-------+----+----------------+-------+

I tried something like

update student_reports 
set total= sum(marks) 
where std_id=std_id;

but does not work, any help is greatly appreciated especially one that does not require changing the table structure

For the first table above, with a single course_id, the code by GILEYS calculated the total perfectly

update student_reports r, ( select std_id, sum(marks) as total from student_reports group by std_id ) t set r.total = t.total where r.std_id = t.std_id;

My next question is what if i add another course_id value, to make it (40,43) to the table, how can i do the new totals

+--------+-----------+-------+----+----------------+-------+

+--------+-----------+-------+----+----------------+-------+

| 32 | 40 | 30.00 | 22 | Elective Maths | 0 |

| 32 | 40 | 25.00 | 12 | Elective Maths | 0 |

| 39 | 40 | 30.00 | 22 | Elective Maths | 0 |

| 39 | 40 | 20.00 | 12 | Elective Maths | 0 |

| 32 | 43 | 40.00 | 22 | Core Maths | 0 |

| 32 | 43 | 20.00 | 12 | Core Maths | 0 |

| 39 | 43 | 45.00 | 22 | Core Maths | 0 |

| 39 | 43 | 40.00 | 12 | Core Maths | 0 |

+--------+-----------+-------+----+----------------+-------+

I tried something like

update student_reports r, (select std_id,course_id,subject, sum(marks) as total from student_reports group by std_id) t set r.total = t.total where r.std_id = t.std_id AND r.course_id=t.course_id AND r.subject=t.subject;

but is not giving out the totals as i would expert, please need your help again

to achieve the desired results below

+--------+-----------+-------+----+----------------+-------+

+--------+-----------+-------+----+----------------+-------+

| 32 | 40 | 30.00 | 22 | Elective Maths | 55 |

| 32 | 40 | 25.00 | 12 | Elective Maths | 55 |

| 39 | 40 | 30.00 | 22 | Elective Maths | 50 |

| 39 | 40 | 20.00 | 12 | Elective Maths | 50 |

| 32 | 43 | 40.00 | 22 | Core Maths | 60 |

| 32 | 43 | 20.00 | 12 | Core Maths | 60 |

| 39 | 43 | 45.00 | 22 | Core Maths | 95 |

| 39 | 43 | 40.00 | 12 | Core Maths | 95 |

+--------+-----------+-------+----+----------------+-------+

You want to update any record in the table or just fetch the total marks of any student? — Sahil, Dec 09 '15 at 16:36
may be duplicate of http://stackoverflow.com/questions/653826/update-with-sum-in-mysql (but as you want only for one ID, it may work with a subquery) — Blag, Dec 09 '15 at 16:43

gmiley · Accepted Answer · 2015-12-09T18:01:06.257

0

Try this:

update student_reports 
set total = (
    select sum(marks) 
    from student_reports 
    where std_id = 32
) 
where std_id = 32;

Or if you want the entire table updated:

    update student_reports 
    set total = t.total
    from (
        select std_id, sum(marks) as total
        from student_reports 
        group by std_id
    ) t
   where t.std_id = student_reports.std_id;

Edit, the above may not work on MySQL, you can try the following:

update student_reports r, (
    select std_id, sum(marks) as total
    from student_reports 
    group by std_id
) t 
set r.total = t.total 
where r.std_id = t.std_id;

edited Dec 09 '15 at 18:01

answered Dec 09 '15 at 16:36

gmiley

6,531
1
13
25

I used your code as follows, hope its correct **update student_reports set total = t.total from (select std_id, sum(marks) as total from student_reports group by std_id) t where t.std_id = student_reports.std_id;** but had an error as follows **ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from (select std_id, sum(marks) as total from student_reports' at line 1** – cammpopp Dec 09 '15 at 17:53
Updated answer. The one you tried may not be compatible with mysql, try the last one. – gmiley Dec 09 '15 at 18:01
Thank you so so much GMILEY, your last code worked like a charm. Will get in touch if there are new developments, More "grease to your elbow" – cammpopp Dec 09 '15 at 18:29
My next question is what if i add another course_id value, to make it (40,43) to the table, how can i do the new totals – cammpopp Dec 11 '15 at 09:04
You would probably want to add a trigger to the table to update that column or change that column to a derived or calculated column if you are looking to have that field always up to date, otherwise you will need to run that script each time. – gmiley Dec 11 '15 at 11:38

score 0 · Answer 2 · answered Dec 09 '15 at 16:55

0

Having a 'total' column might cause grief because it will become wrong as soon as new data is inserted for a given id. This query will give you the same result without needing to run an update every time you get new data. If you need it to act like a table you can look into creating a view.

SELECT t.std_id, t.course_id, t.marks, t.mp, t.subject, c.total
FROM student_reports AS t
JOIN (SELECT std_id, SUM(marks) AS total
      FROM student_reports
      GROUP BY std_id) AS c ON c.std_id = t.std_id

answered Dec 09 '15 at 16:55

Andrew

4,574
26
31

Your code works but did not update the values in the 'total' column, Thanks so much for your help, will contact when there are new developments – cammpopp Dec 09 '15 at 18:31
My next question is what if i add another course_id value, to make it (40,43) to the table, how can i do the new totals – cammpopp Dec 11 '15 at 09:05
That was kind of the point I was trying to make. If you have a totals column, it will be wrong when you add data. With a query (or view) it's always correct as of when you run the query. – Andrew Dec 14 '15 at 22:57

MYSQL UPDATE TABLE ERRORS

2 Answers2