SQL stitching 2 unrelated tables into 2 columns for batch updates to another table

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) Rindexfrom So_master where websit Esysno =6 and Soamt >=800 and Lotteryno is null and  OrderDate >= ' 2016/09/22 00:00:00 ' and orderdate< ' 2016/09/ 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) Rindexfrom so_master_lottery where  status=0 a nd 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 table abbreviation so,so_master_lottery form for short, SML,

1. First query the so table as the update condition, and then use the row_number sort to get the sequence

2. Query the SML table column as the update value, and then use the row_number sort to get the sequence

3. Associate the so table and the SML table with the serial number to obtain the required update conditions and update values, as follows:

Select SM. Lotteryno,sm. Sysno,t2. Lotterynofrom so_master sminner Join (select Sysno,row_number () over (order by sysno ASC) Rindexfrom So_master where websit Esysno =6 and Soamt >=800 and Lotteryno is null and  OrderDate >= ' 2016/09/22 00:00:00 ' and orderdate< ' 2016/09/ 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) Rindexfrom so_master_lottery where  status=0 a nd websitesysno = 6) t2 on T1.rindex=t2.rindexwhere Sm. sysno=1422087 and SM. Lotteryno is nullorder by T1. Sysno Asc,t2. Lotteryno ASC

4. Use the SQL execution syntax for bulk update, make an internal connection, update the condition table by updating the primary Table So_master Table Association T1, and then T2 get the updated value, T1 and update the main table So_master through the Foreign Key Association, T2 is T1 according to the T2 Ordinal Association, and then add all the query criteria ，

The main is the column with the updated value Lotteryno

Other SQL Introduction:

The following SQL is updated data according to Row_number, for reference only

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 are 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 stitching 2 unrelated tables into 2 columns for batch updates to another table