I have made a table “winner” from table “DIBBS.nsn” using

SELECT *
into "winner"
  FROM (
                SELECT  nsn_part_number, 
                        reason, 
                        ROW_NUMBER() OVER(PARTITION BY nsn_part_number order by date_accessed desc) rn
                    FROM DIBBS.nsn
                    where order_status = 'WON'
              ) a
WHERE rn = 1

–takes all nsn part numbers and reason for any status that is “WON” Also with duplicates, it takes the latest dated part number and reason.

I am trying to figure out how to keep this table updated with the DIBBS.nsn. The DIBBS.nsn will have new rows added everyday.

What I have tried:

merge into winner as target
using dibbs_nsn as source
on (target.nsn_part_number = source.nsn_part_number)
and (source.order_status = 'WON')
when not matched
then insert (nsn_part_number, reason)
    values (source.nsn_part_number, source.reason)

This insert doesn’t filter out any of the duplicates, which makes sense but don’t really know how to put that in.

Currently my work around for this is that I delete the table and re make the table which in turns updates “winner” using the updates DIBBS.nsn.

I believe theres a query to update, maybe upsert? I looked into it but am a little confused because of the way my select into only takes the most recent dated part number.

What I need:
Query that I can run, use in task manager daily to update the “winner” table after new rows have been added to DIBBS.nsn.