MySQL - updating all records to match max value in group

mysql

Solution

UPDATE  t_transaction tu
JOIN    (
        SELECT  code, MAX(flag) AS flag
        FROM    t_transaction
        GROUP BY
                code
        ) t
ON      tu.code = t.code
SET     tu.flag = t.flag

Problem

I have a transaction table like so ``` id , name , code , flag 1 , john , 1234-3, 2 2 , joe , 1111-2, 1 3 , paul , 1234-3, 3 4 , asdf , 1234-3, 3 5 , asdf , 1111-2, 5 6 , asdf , 1234-3, 8 7, asdf , 1234-3, 0 ``` Basically, what I want to do is set all numbers in the 'flag' feild to the max value related to a specific code. So in in the case of code 1234-3 it should update all flags with that code with flag num 8 in the case of 1111-2, it needs to update all flags with that code with 5. I want to transform it into this table ``` id , name , code , flag 1 , john , 1234-3, 8 2 , joe , 1111-2, 5 3 , paul , 1234-3, 8 4 , asdf , 1234-3, 8 5 , asdf , 1111-2, 5 6 , asdf , 1234-3, 8 7 , asdf , 1234-3, 8 ``` I'd like to do this in MySQL purely if possible. There is a very large set of data.

Original source