MySQL - update all records to match last

Question

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

Basically, what I want to do is get the last instance of a code check the flag and update all previous flags with the same code with the last flag num.

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

I'd like to do this in MySQL purely if possible. There is a very large set of data.

Answer 1

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

Make sure you have an index on (code), if your table is InnoDB and id is a PRIMARY KEY, or on (code, id) if your table is MyISAM or id is not a PRIMARY KEY.

This index will be used both both for JOIN's and for efficient GROUP BY.

To set the value to the MAX(flag) (not the last flag):

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