求一存储过程或sql语句,批量动态更新,mysql,数据量大
求一个存储过程或sql语句,可以根据table1表中的uid,到table2中查出相应的dtype,更新到table1中的dtype中
mysql数据库,table1表中记录200条
table2表中记录220万
table1中有字段uid,dtype
其中uid是有值的
uid有重复数据,非空
dtype在这个表中是空的,目的就是往这里边加入值
uid dtype
110
120
120
130..
table2中有字段uid,dtype
uid为唯一
dtype有重复数据,非空
uid dtype
110 a
120 a
130 b
140 c
150 c
存储过程执行或sql语句后,table1中的数据为
uid dtype
110 a
120 a
120 a
130 b
..
之前一CSDN上的哥们写的update table1 set dtype=(select dtype from table2 where table1.uid=table2.uid)
数据量小没问题
但是table1表中记录200条
table2表中记录220万
我执行了半个小时也没过去
I5的U
8G内存
试试这个看行不行
-
SQL code
select dtype INTO #tmptble from table2 where uid in (select uid from table2) update table1 set dtype=(select top 1 dtype from #tmptble where table1.uid=#tmptble.uid)
楼上有误。
-
SQL code
select dtype INTO #tmptble from table2 where uid in (select uid from [color=#FF0000]table1[/color]) update table1 set dtype=(select top 1 dtype from #tmptble where table1.uid=#tmptble.uid)
-
SQL code
create table tab1_uid as
select distinct uid from table1;
create index idx1 on tab1_uid(uid);
create table tab2_uid as
select a.uid , a.dtype
from table2 a inner join tab1_uid b on a.uid = b.uid
update table1 a, tab2_uid b
set a.dtype = b.dtype
where a.uid = b.uid
