mysql 更新为最大值_MySQL-更新所有记录以匹配组中的最大值

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.

解决方案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