首页 > 代码库 > SQL 将2张不相关的表拼接成2列，批量更新至另一张表
SQL 将2张不相关的表拼接成2列，批量更新至另一张表
update SO_Master set LotteryNo=t2.LotteryNo,UpdateTime=GETDATE() --select sm.LotteryNo,sm.SysNo,t2.LotteryNofrom SO_Master sminner join ( select SysNo,ROW_NUMBER() over(order by sysno asc) rIndex from SO_Master where WebSiteSysNo =6 and SOAmt >=800 and LotteryNo is null AND OrderDate >=‘2016/09/22 00:00:00‘ and OrderDate<‘2016/09/24 00:00:00‘ and Status not in(-1,-2,-3)) t1 on t1.SysNo=sm.SysNoinner join ( select LotteryNo,ROW_NUMBER() over(order by sysno asc) rIndex from SO_Master_Lottery where status=0 and WebSiteSysNo = 6) t2 on t1.rIndex=t2.rIndexwhere sm.SysNo=1422087 and sm.LotteryNo is null--order by t1.SysNo asc,t2.LotteryNo asc
SO_master表简称SO,SO_Master_Lottery表简称SML,
1.首先将SO表列查询出来作为更新条件，然后使用Row_Number排序获得序列
2.将SML表列查询出来作为更新值，然后使用Row_number排序获得序列
3.通过序列号将SO表和SML表关联起来,获得所需的更新条件和更新值，如下代码：
select sm.LotteryNo,sm.SysNo,t2.LotteryNofrom SO_Master sminner join ( select SysNo,ROW_NUMBER() over(order by sysno asc) rIndex from SO_Master where WebSiteSysNo =6 and SOAmt >=800 and LotteryNo is null AND OrderDate >=‘2016/09/22 00:00:00‘ and OrderDate<‘2016/09/24 00:00:00‘ and Status not in(-1,-2,-3)) t1 on t1.SysNo=sm.SysNoinner join ( select LotteryNo,ROW_NUMBER() over(order by sysno asc) rIndex from SO_Master_Lottery where status=0 and WebSiteSysNo = 6) t2 on t1.rIndex=t2.rIndexwhere sm.SysNo=1422087 and sm.LotteryNo is nullorder by t1.SysNo asc,t2.LotteryNo asc
4.使用批量更新的SQL执行语法，进行内连接，由更新主表SO_master表关联t1更新条件表，然后内链接t2获得更新值，t1和更新主表SO_master通过外键关联，t2是根据t1的序号关联t2的，然后加上所有查询条件，
主要是带上更新值的列LotteryNo
其他sql介绍:
下面这个Sql是按照Row_number更新数据的，仅供参考
update SO_Master set LotteryNo =t1.LotteryNo,UpdateTime=GETDATE() from SO_Master sminner join(select SysNo, 168799-ROW_NUMBER()over(order by sysno asc) LotteryNo from SO_Master where WebSiteSysNo =6 and SOAmt >=800 and LotteryNo is null AND OrderDate >=‘2016/09/22 00:00:00‘ and OrderDate<‘2016/09/24 00:00:00‘ and Status not in(-1,-2,-3)) t1 on sm.SysNo=t1.SysNowhere sm.LotteryNo is null
SQL 将2张不相关的表拼接成2列，批量更新至另一张表
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。