update Data set groupid = DelimRowID where RowNo = 1This took 3 minutes and 10 seconds. The problem with triangular join is the number of records to consider. True that the where clause excludes unwanted records in the end but still it considers them inside of the CTE, the one which has partitioned row\_number. The number of records to consider for just 30,000 records in the table happens to be over 122 million! (122,742,273 to be exact). This is the downside of the triangular join solutions. When I tested the quirky update solution, it took less than a blink of an eye for the whole thing, including creation of temp table, populating it with 30,000 records, updating it and finally updating the table from its join with that temp table. I will post the data creation part in the next comment due to lack of space.
use AdventureWorks; go create table dbo.Test ( rowid int not null constraint PK_test primary key clustered, groupid int null, [text] char(1) not null ); go insert into dbo.Test (rowid, [text]) select top 30000 row_number() over (order by a.[object_id], a.column_id, t.rowid) N, t.[text] from ( select 1 rowid, 'H' [text] union all select 2, 'I' union all select 3, '\' union all select 4, 'M' union all select 5, 'Y' union all select 6, '\' union all select 7, 'N' union all select 8, 'A' union all select 9, 'M' union all select 10, 'E' union all select 11, '\' ) t cross join sys.all_columns a goThis creates records like this:
rowid groupid text ----- ------- ---- 1 NULL H 2 NULL I 3 NULL \ 4 NULL M 5 NULL Y 6 NULL \ 7 NULL N 8 NULL A 9 NULL M 10 NULL E 11 NULL \ 12 NULL H 13 NULL I 14 NULL \ 15 NULL M 16 NULL Y 17 NULL \ 18 NULL N 19 NULL A 20 NULL M -- etc
No one has followed this question yet.