update 表a a
inner join
(select col2,count(*) as nu from 表a group by col2 having count(*)=2) b
on a.col2=b.col2
set a.col3=a.col3+a.id where a.col=12 ;
inner join
(select col2,count(*) as nu from 表a group by col2 having count(*)=2) b
on a.col2=b.col2
set a.col3=a.col3+a.id where a.col=12 ;
感谢,这样可以。不过我这个表a 数据比较大,col2 有很多相同的,但是只有在等于12的时候,更新COL3join
(select col2,count(*) as nu from 表a group by col2 having count(*)=2)这样的话,显得查询量太大了,有没其它更好点的。col2有索引,只需要判断两个条件,是否等于12, 等于12的行是否大于1
update 表a set col3=col3+id where col=12 and
(select counts from (select count(*) as counts from 表a where col2= 12) as c))>1 ;
update 表a a
inner join
(select col2,count(*) as nu from 表a
where col=12 group by col2 having count(*)=2) b
on a.col2=b.col2
set a.col3=a.col3+a.id ;
inner join
(select col2 from 表a b where col=12 and
2=(SELECT COUNT(*) FROM 表a WHERE b.col=col )
) b1
on a.col2=b1.col2
set a.col3=a.col3+a.id ;
set u.col3=u.col3+u.id
where v.id is null创建ID的索引,
创建 col2的索引。