The performance of Chris' solution is fantastic.

If we use his setup script, we can use the following code to perform the update:

IF OBJECT_ID('tempdb..#t2') IS NOT NULL drop table #t2

SELECT DISTINCT ID

INTO #t2

FROM #Table1 a

CROSS APPLY dbo.DelimitedSplit8K(a.Field1, ' ') split

WHERE EXISTS (SELECT 1 FROM #Lookup b WHERE b.LookDesc = split.Item)

UPDATE #Table1

SET Field2 = 'Yes'

WHERE ID in (

select ID from #t2

)

This code executes in less than 100ms on my machine. I expect similar results for the real data. Not bad to go from 45 minutes to sub-second performance!

Well done!

/SG