1

Topic: Summation of a field from two records

There is a basis of students which has child tables. One student was entered by two times. It is necessary to merge two cards in one.
With change id the student in child tables of a problem is not present, but it is necessary also  balance.
And here at me full .
Tried different variants, but MySQL all the same swears

UPDATE Students
SET std_balance = (SELECT SUM (std_balance) FROM Students WHERE std_id = 5 or std_id = 7) WHERE std_id = 5;

You cannot specify target table ' Students ' for update in FROM clause

UPDATE Students s
SET std_balance = (SELECT SUM (std_balance) FROM Students WHERE std_id = 5 or std_id = 7) WHERE s.std_id = 5;

You cannot specify target table ' s ' for update in FROM clause

UPDATE Students
SET std_balance = (SELECT SUM (s.std_balance) FROM Students s WHERE s.std_id = 5 or s.std_id = 7) WHERE s.std_id = 5;

You cannot specify target table ' Students ' for update in FROM clause
Kick in the necessary side who knows.

2

Re: Summation of a field from two records

It is necessary to use multitabular form UPDATE:

UPDATE Students, (SELECT std_id, SUM (std_balance) std_balance
FROM Students
GROUP BY std_id
) subquery
SET Students.std_balance = subquery.std_balance
WHERE Students.std_id = subquery.std_id

3

Re: Summation of a field from two records

Something I brake. Same groups balance in everyone id the student, and it and so one. I need to merge balance of two id in one.

4

Re: Summation of a field from two records

Eventually reached request

UPDATE Students A JOIN Students B ON B.std_id = 7
SET
A.std_balance = A.std_balance + B.std_balance
WHERE
A.std_id = 5;

Like works, but can eat what reefs?

5

Re: Summation of a field from two records

Alex-777 wrote:

I need to merge balance of two id in one.

Find field or set of fields which are unique for the student, and at the same time defining. Also use them for communication instead of WHERE Students.std_id = subquery.std_id and for grouping instead of GROUP BY std_id.