MySQL merge amounts in a 2 rows

Question

I'm looking to create a sql statement that will update a large set of data.

What I have is a table like

id,   transid,   amount,   narative1,  narative 2, total,    active
1     1234       23.2        NULL        NULL        NULL      1
2     1234       120.33      NULL        NULL        NULL      1
3     1235       98.00       NULL        NULL        NULL      1

When there are two rows with the same transid I need to total them put the result in the total column of the first one with that transid and put the second amount in naritive2 of the first instance as well as make the second one inactive. It should ignore single rows for a transid.

The result of what I want to do should be:

id,   transid,   amount,   narative1,  narative 2, total,      active
1     1234       23.2        NULL        120.33      143.53     1     
2     1234       120.33      NULL        NULL        NULL       0
3     1235       98.00       NULL        NULL        NULL       1

I know a bit of a thong twister but..

Ideally I'd like to do this in just a MySQL statements. So I don't mind having to do multiple sql statements but I want to avoid connecting it to PHP etc. Its a very large set of data.

Answer 1

This will update only those transactions that have exactly 2 rows (not 1 and not 3 or more).

UPDATE  mytable mtu
JOIN    (
        SELECT  minid, maxid, mtmin.amount AS minamt, mtmax.amount AS maxamt
        FROM    (
                SELECT  MIN(id) AS minid, MAX(id) AS maxid
                FROM    mytable mti
                GROUP BY
                        transid
                HAVING  COUNT(*) = 2
                ) mt
        JOIN    mytable mtmin
        ON      mtmin.id = minid
        JOIN    mytable mtmax
        ON      mtmax.id = maxid
        ) mts
ON      id IN (minid, maxid)
SET     narative2 = CASE id WHEN minid THEN minamt ELSE NULL END,
        total = CASE id WHEN minid THEN minamt + maxamt ELSE NULL END,
        active = (id = minid)