CODEWITHSUNDEEP
mysql
Derek Organ
Derek Organ

Reputation: 8483

MySQL - updating all records to match max value in group

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.

Upvotes: 2

Views: 2163

Answers (2)

gahooa
gahooa

Reputation: 137472

It can be accomplished very simply in two queries using mysql variables. I would point out that Quassnoi has a great answer that may work better for your application. But this is useful to keep in mind:

If you are using a transactional storage engine (innodb):

START TRANSACTION;
SELECT @myvar := MAX(flag) FROM t WHERE code = '...'
UPDATE t SET flag = @myvar WHERE code = '...';
COMMIT;

If you are using a non-transactional storage engine (myisam):

LOCK TABLES t WRITE;
SELECT @myvar := MAX(flag) FROM t WHERE code = '...'
UPDATE t SET flag = @myvar WHERE code = '...';
UNLOCK TABLES;

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425683

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

Upvotes: 8

Related Questions