I have a table (t1) with a unique list of patients. I have another table (t1_Backup) with a duplicative list of patients and details. I would like to essentially perform an inner join to update t1, which has empty value sets for the detail columns contained in t1_Backup. t1_Backup also has some records I am going to remove in the join.
MERGE INTO t1
USING (SELECT * FROM
(SELECT t1_Backup.*
,RANK() OVER (PARTITION BY Patient_ID ORDER BY Col1 DESC, Col2 DESC, ...) AS Pick
FROM t1_Backup
WHERE Active_Status = 'A')
WHERE Pick = 1)
t2 ON (t1.Patient_ID = t2.Patient_ID)
WHEN MATCHED THEN UPDATE
SET
t1.Col1 = t2.Col2
t1.Col2 = t2.Col2
....
ad infinitum (not literally, but as you might guess I am updating just a ton of columns — this whole exercise is an elaborate attempt to remove duplicates from t2. The data is complex but if anyone has suggestions for other approaches I am all ears.)
For those paying close attention, you might have noticed that the Active_Status = 'A' criteria could have a restrictive effect, causing me to have some Patient_IDs which are not updated. Given my requirements, I would actually like to simply drop these rows in one swift, neat step.
My question, in short, is if I can add a line of code like WHEN NOT MATCHED THEN DELETE?
I want to know what to expect before running anything like that. Thanks!
Advertisement
Answer
Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET … WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.