sql - MYSQL join update internal steps -

use table , data:

create table test.tem(a int,b int,index (a),index (b)); insert test.tem values(1,2),(1,1),(1,null),(2,3); 

now data should be:

+------+------+ |    | b    | +------+------+ |    1 |    2 | |    1 |    1 | |    1 | null | |    2 |    3 | +------+------+ 

i want update column b min(b) group column a.
have known 1 correct sql is:

update tem t1   join (select a,min(b) m tem group a) t2     using (a) set t1.b = t2.m;  

producing correct result is:

+------+------+ |    | b    | +------+------+ |    1 |    1 | |    1 |    1 | |    1 |    1 | |    2 |    3 | +------+------+ 

however, takes 5 minutes update in 1 table 4.5 million records using sql query.

so, have 1 sql of myself:

update test.tem t1   join test.tem t2     on t1.a = t2.a set t1.b = t2.b t1.b > t2.b      or t1.b null; 

but gets incorrect result:

+------+------+ |    | b    | +------+------+ |    1 |    1 | |    1 |    1 | |    1 |    2 | |    2 |    3 | +------+------+ 

i think reason how mysql work when update. can tell me how incorrect result come out? if fix my sql too.

for "query not updating correctly rows":

you want update column b minimum of b rows having same a

you proposed use following join that:

update test.tem t1   join test.tem t2     on t1.a = t2.a set t1.b = t2.b t1.b > t2.b      or t1.b null; 

contrary may think, join not perform 1-1 join. in fact many-to-many join since as said yesterday don't use primary key (nor non-null unique key) in join clause.

in fact, rewriting query as select understand problem:

select t1.a t1a, t1.b t1b, t2.a t2a,t2.b t2b tem t1 join tem t2     on t1.a = t2.a t1.b > t2.b      or t1.b null;  +------+---------+------+--------+ | t1a  |  t1b    | t2a  |  t2b   | +------+---------+------+--------+ |   1  | (null)  |   1  | 2      | |   1  | 2       |   1  | 1      | |   1  | (null)  |   1  | 1      | |   1  | (null)  |   1  | (null) | +------+---------+------+--------+ 

http://sqlfiddle.com/#!2/856a7/8

as see now, row (1, null) match (1, 1), (1, 2) , (1, null). depending (non-deterministic) order of execution of query, might assign of 3 possible values b ('m not sure that, maybe updating several times). extend, have been lucky find "wrong" result while testing!

i hope explain little bit more why query not produce expected result. since multi-table update statements don't allow order by nor group by clauses, of myself, find "good" result, don't see many other options finding minimum first through sub-query...