sql server - Optimization of a Recursive SQL Query -

i have large table need update. defined following example (but mine massive 1m+ rows , more columns)...

create table t     ([errors] varchar(4), [mrn] int, [epi] varchar(13), [wd] varchar(4));  insert t     ([errors], [mrn], [epi], [wd]) values (null, 107, 'ip00001070001', 'amum'), (null, 107, 'ip00001070001', 'amum'), (null, 107, 'ip00001070001', 'knap'), (null, 107, 'ip00001070002', 'knap'), (null, 107, 'ip00001070002', 'knap'), (null, 107, 'ip00001070002', 'knap'), (null, 107, 'ip00001070002', 'knap'), (null, 381, 'ip00003810001', 'eaus'), (null, 381, 'ip00003810001', 'eaus'), (null, 381, 'ip00003810003', 'dock'), (null, 381, 'ip00003810003', null), (null, 45, 'ip00000450001', 'ases'), ('__', 45, 'ip00000450002', null), ('__', 381, 'ip00003810002', null); 

i need update wd columns of records have null wd values match wd value of first entry if ordered [mrn] , [epi]. example, required output be:

errors  mrn epi             wd null    107 ip00001070001   amum null    107 ip00001070001   amum null    107 ip00001070001   knap null    107 ip00001070002   knap null    107 ip00001070002   knap null    107 ip00001070002   knap null    107 ip00001070002   knap null    381 ip00003810001   eaus null    381 ip00003810001   eaus null    381 ip00003810003   eaus null    381 ip00003810003   eaus null    45  ip00000450001   ases __      381 ip00003810003   eaus __      45  ip00000450002   ases __      381 ip00003810002   eaus 

with edited records @ bottom. want. however, method slllloooowww... slow, , reason, looping on the entire set. questions having indexed target table:

1. how can optimize query/operation?
2. do need recursion here?

here entire test query set aid willing help:

if exists (     select name      sys.tables      name = n't') drop table [t]  go  create table t     ([errors] varchar(4), [mrn] int, [epi] varchar(13), [wd] varchar(4));  insert t     ([errors], [mrn], [epi], [wd]) values     (null, 107, 'ip00001070001', 'amum'),     (null, 107, 'ip00001070001', 'amum'),     (null, 107, 'ip00001070001', 'knap'),     (null, 107, 'ip00001070002', 'knap'),     (null, 107, 'ip00001070002', 'knap'),     (null, 107, 'ip00001070002', 'knap'),     (null, 107, 'ip00001070002', 'knap'),     (null, 381, 'ip00003810001', 'eaus'),     (null, 381, 'ip00003810001', 'eaus'),     (null, 381, 'ip00003810003', 'dock'),     (null, 381, 'ip00003810003', 'dock'),     (null, 45, 'ip00000450001', 'ases'),     ('__', 381, 'ip00003810003', null),     ('__', 45, 'ip00000450002', null),     ('__', 381, 'ip00003810002', null);  if exists (select *             sys.indexes             name='idxeetest' , object_id = object_id('t')) drop index [idxeetest] on [t]; go  create nonclustered index [idxepiipwardload]  on [t] ([mrn], [epi]) go  declare @sql nvarchar(max) declare @mrn int declare @epi nvarchar(16) declare @get_rec cursor set @get_rec = cursor     select mrn, epi      t      errors not null open @get_rec fetch next @get_rec @mrn, @epi  while @@fetch_status = 0 begin     set @sql =          'declare @wd varchar(4); ' +         'select top 1 @wd = wd ' +          'from t ' +          'where mrn = ' + convert(varchar, @mrn) + ';' +         'update t ' +         'set wd = @wd ' +          'where mrn = ' + convert(varchar, @mrn) + ' , epi = ''' + @epi + ''''     exec(@sql);      fetch next     @get_rec @mrn, @epi  end close @get_rec deallocate @get_rec go  if exists (select *             sys.indexes             name='idxeetest' , object_id = object_id('t')) drop index [idxeetest] on [t]; go 

thanks time.

i think understand trying do. considerably if posted expect desired output confirm. rules , data posted should work.

update t1 set wd = u.newwd t t1 cross apply (     select top 1 wd newwd     t t2     t2.mrn = t1.mrn     order t2.epi )u t1.errors not null