mysql update by select

update (select iRet, 12345 as uTime, uCmd, uTargetIp, uFromIp, sum(uCount) as uCount, sum(uExeTimeSum) as uExeTimeSum, max(uExeTimeMax) as uExeTimeMax, min(uExeTimeMin) as uExeTimeMin from tbStatDailyCommand_5999 where uTime>1285477200 and uTime<2285477200 group by iRet, uCmd, uTargetIp, uFromIp) as a,test as b set b.uCount=a.uCount, b.uExeTimeSum=a.uExeTimeSum where b.iRet=a.iRet AND b.uTime=a.uTime AND b.uCmd=a.uCmd AND b.uTargetIp=a.uTargetIp AND b.uFromIp=a.uFromIp;

insert into test (select iRet, 12345, uCmd, uTargetIp, uFromIp, sum(uCount), sum(uExeTimeSum), max(uExeTimeMax), min(uExeTimeMin) from tbStatDailyCommand_5999 where uTime>1285477200 and uTime<2285477200 group by iRet, uCmd, uTargetIp, uFromIp);

 

正解：

insert into test (select iRet, 12345, uCmd, uTargetIp, uFromIp, sum(uCount) as uCount, sum(uExeTimeSum) as uExeTimeSum, max(uExeTimeMax) as uExeTimeMax, min(uExeTimeMin) as uExeTimeMin from tbStatDailyCommand_5999 where uTime>1285477200 and uTime<2285477200 group by iRet, uCmd, uTargetIp) on duplicate key update uCount=values(uCount), uExeTimeMax = values(uExeTimeMax), uExeTimeMin = values(uExeTimeMin), uExeTimeSum = values(uExeTimeSum);

 

 insert into tbCollectAppInfo values(101,5694,123) on duplicate key update uTime=case when 1285573156>uTime then 1285573156 else uTime end;;