Since the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..

Here is a "working" version of the script you have shared. 

Create table customer
(custid int constraint ct_id primary key,
cust_name nvarchar(100),
cust_addr1 nvarchar(100),
cust_addr2 nvarchar(100),
city...


update table a
     set a.date=(select max(b.date) from table b where b.stat_cd=a.stat_cd)


Can you share a tiny yet complete DDL statements DML statements for Order and Customer table and share how the output should look like?

Hmm, there is already a datediff function which will tell you the difference between two dates. And it can return the values in whichever format you like the difference -->...

If the Insert statement you are running comes from a single sql statement, then you wouldn't need to manually lock any records?

eg:

BEGIN TRANSACTION...

You could try the following. 
What i do is recursively look for patterns(patindex(%[^a-z0-9]%)) which is non alphanumeric and replace it with null as you can see in the source_data and...

Just to understand better

Could you share the sample rows for source_table and destination_table

I suppose your destination table has col1 as the primary key?

You can make use of recursive cte to get the output. The expected results as mentioned by you is slightly incorrect.

with data
as (select row_number() over(order...

Per this example

Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVE
Condition 1: If a record count has no update but a remove then insert

with data
as (SELECT *
  FROM (VALUES (101,10,10)
      ,(102,20,30)
      ,(103,30,30)
      ,(104,12,12)
      ,(105,18,30)
      ,(106,10,10)
      ...

Choose WHERE fitting='Y' to get the records which have been matched successfully

By defining a recursive cte this problem can be solved.
First define an anchor row for the cte where orders=1.
I go on to define 3 new columns

/* Result for this query should be - 'ABC'
 This can be done as follows*/
select Distinct CompanyName
from #Comapanytemp
where (