根据一个表更新另外一个表/多表更新

#test、test2为同样的2张表，把A表数据更新到B表
update A t1 set t1.project = (select project from B t2  where t1.product=t2.product) 
update A t1,B t2 set t1.project = t2.project  where t1.product=t2.product






#-----------分别统计记录中每个产品对应的记录数，并更新对应的统计表prdCount-------------------------
#-----------------------------------------------------------------------
#5万条数据约需要0.09ms
#18万条数据：0.573ms/0.414ms/0.431ms/0.399ms
update prdCount  set total = (select count(*) as total from zt_build tab group by product  having tab.product = prdCount.product) 
#------------------------------------------------------------------------




#------------------------------------------------------------------------
#5万条数据约需要0.008ms
#18万条数据：0.045ms/0.031ms/0.032ms/0.038ms
update prdCount  A, (select product,count(*) as total from zt_build tab group by product  ) B 
set A.total=B.total
where A.product = B.product
#------------------------------------------------------------------------




#------------游标更新---------------------------------------------
#5万条数据约0.008ms
#18万条数据约0.041ms/0.034ms/0.038ms/0.031ms
#创建存储过程，通过游标更新数据
drop procedure if exists update2Tab;
CREATE PROCEDURE `update2Tab`()
BEGIN
 DECLARE  no_more_record INT DEFAULT 0;
 DECLARE  v_product int;
 DECLARE  v_count int;
 DECLARE  cur_record CURSOR FOR   SELECT  product,count(*) as tatol from zt_build group by product;  /*首先这里对游标进行定义*/
 DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/


 OPEN  cur_record; /*接着使用OPEN打开游标*/
 FETCH  cur_record INTO v_product, v_count; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
 WHILE no_more_record != 1 DO
    update prdCount set total = v_count
     where product = v_product;
     FETCH  cur_record INTO v_product, v_count;
 END WHILE;
 CLOSE  cur_record;  /*用完后记得用CLOSE把资源释放掉*/
END;


call update2Tab();
#--------------------------------------------------
#子查询创建产品统计表prdCount
create table prdCount(select distinct product from zt_build   );
#新增一个统计列
alter table prdCount add column total int;


select count(*) from zt_build;




update prdcount set total = 0;
select * from prdcount;


#子查询创建表
create table zt_build2(select * from zt_build union select * from zt_build )


#子查询插入
insert into zt_build (`product`, `branch`, `project`, `name`, `scmPath`, `filePath`, `date`, `stories`, `bugs`, `builder`, `desc`, `deleted`) 
 (SELECT `product`, `branch`, `project`, `name`, `scmPath`, `filePath`, `date`, `stories`, `bugs`, `builder`, `desc`, `deleted` FROM `zt_build`)