Convert to CTE or Window function or a set or a merge statement Is anyone knows how to convert the below code using a CTE or Window function:

UPDATE a
SET a.linked_pmt_id = b.linked_pmt_id, a.looptag = '1Mb-NBCBZ'
FROM tblname_KS a 
    JOIN (SELECT * FROM tblname_KS with (NOLOCK) WHERE pmt_id = linked_pmt_id) b
    ON ISNULL(a.prop_name,'NULL') = ISNULL(b.prop_name,'NULL') AND 
       ISNULL(a.prop_addr1,'NULL') = ISNULL(b.prop_addr1,'NULL') AND 
       a.prop_city = b.prop_city
WHERE   a.linked_pmt_id IS NULL AND
        (a.status &lt;&gt; 'U' AND  a.addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND a.addr_type IS NOT NULL) AND
        a.prop_city IS NOT NULL AND
        a.prop_zip IS NULL    
GO


Thanks
Without knowing your RDBMS, and with the very vague description you're giving, here's my initial approach.

It's basically taking your Subquery and converting it to CTE.

I'd suggest a more elaborate WHERE clause on that CTE, as right now it returns All rows from that table (which can take a while depending on how much data you have).

Things get a bit hairy, as you're inner joining a table to itself, and using that join to update THAT same table.  I'd tread very lightly and ensure you have a good test environment.

Maybe converting "a" to CTE is a better approach than converting "b" to CTE (since "a" actually has a WHERE clause).  Or maybe having BOTH of them as CTE is an even better approach to simplify the UPDATE.

;WITH CTE_tblsname_KS
AS
(SELECT linked_pmt_id
      ,pmt_id
      ,prop_name
      ,prop_addr1
      ,prop_city
FROM tblname_KS (NOLOCK)
)

UPDATE a
SET a.linked_pmt_id = b.linked_pmt_id
  , a.looptag = '1Mb-NBCBZ'
FROM tblname_KS a 
INNER JOIN CTE_tblsname_KS b
ON ISNULL(a.prop_name,'NULL') = ISNULL(b.prop_name,'NULL') AND 
   ISNULL(a.prop_addr1,'NULL') = ISNULL(b.prop_addr1,'NULL') AND 
   a.prop_city = b.prop_city
WHERE   a.linked_pmt_id IS NULL AND
    (a.status &lt;&gt; 'U' AND  a.addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND a.addr_type IS NOT NULL) AND
    a.prop_city IS NOT NULL AND
    a.prop_zip IS NULL