Performing a merge operation by replacing existing rows - Amazon Redshift

Performing a merge operation by replacing existing rows

When you run the merge operation detailed in the procedure, put all of the steps except for creating and dropping the temporary staging table in a single transaction. The transaction rolls back if any step fails. Using a single transaction also reduces the number of commits, which saves time and resources.

To perform a merge operation by replacing existing rows

  1. Create a staging table, and then populate it with data to be merged, as shown in the following pseudocode.

    CREATE temp table stage (like target); 

INSERT INTO stage 
SELECT * FROM source 
WHERE source.filter = 'filter_expression';

  2. Use MERGE to perform an inner join with the staging table to update the rows from the target table that match the staging table, then insert all the remaining rows into the target table that don't match the staging table.

    We recommend you run the update and insert operations in a single MERGE command.

    MERGE INTO target 
USING stage [optional alias] on (target.primary_key = stage.primary_key)
WHEN MATCHED THEN 
UPDATE SET col_name1 = stage.col_name1 , col_name2= stage.col_name2, col_name3 = {expr}
WHEN NOT MATCHED THEN
INSERT (col_name1 , col_name2, col_name3) VALUES (stage.col_name1, stage.col_name2, {expr});

  3. Drop the staging table. 

    DROP TABLE stage;