优化数据更新

最新推荐文章于 2020-12-21 19:16:18 发布

iteye_1681

最新推荐文章于 2020-12-21 19:16:18 发布

阅读量88

点赞数

文章标签： SQL

本文链接：https://blog.csdn.net/iteye_1681/article/details/81927175

版权

今天写了一条数据更新操作的SQL语句
如下
update shop as t1
set
t1.STREET=
(select distinct (concat(t2.DSCP," ",coalesce(t2.STREET)))
from mall as t2
where t1.MALL_CD=t2.CODE),
t1.STREET_LOC_1=
(select distinct (concat(t2.DSCP_LOC_1," ",coalesce(t2.STREET_LOC_1)))
from mall as t2
where t1.MALL_CD=t2.CODE),
t1.STREET_LOC_2=
(select distinct (concat(t2.DSCP_LOC_2," ",coalesce(t2.STREET_LOC_2)))
from mall as t2
where t1.MALL_CD=t2.CODE);
当时也没考虑很多【数据量比较大】，结果把语句执行好才意识到，效率太低了，一位前辈告诉我其实那个更新语句可以如下所示，效率会提高很多。
update shop as sh, mall as ma set sh.STREET=concat(ma.DSCP,' ',ma.STREET),sh.STREET_LOC_1=concat(ma.DSCP_LOC_1,' ',ma.STREET_LOC_1),sh.STREET_LOC_2=concat(ma.DSCP_LOC_2,' ',ma.STREET_LOC_2) where sh.MALL_CD=ma.CODE
对照比较发现，的确如此。看来以后要注意了！