Hi,

You could consider putting the data from the subquery to temp table

Create table #temp

(

gl_group_id Int

)

insert into #temp

select distinct gl_group_id

from ipadmin.gl_txn

where transacted_on >= convert(datetime, '03/07/2008', 103)

and transacted_on < convert(datetime, '03/07/2008', 103) + 1

and belongs_to_hcare in (100)

--You don't need the GROUP BY, as it makes no sense in this context

--If #temp has few hundred, thousands rows try creating index on gl_group_id column.

update ipa

set defunct = 'Y'

from ipadmin.GL_TXN ipa

inner join #temp t

ON ipa.gl_group_id = t.gl_group_id

Also one of the optimization options would be to create clustered index on ipadmin.gl_txn(transacted_on) column, this would speed up the datetime range query.