メニュー
Amazon Redshift
データベース開発者ガイド (API Version 2012-12-01)

既存の行を置き換えてマージ操作を実現する

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.

    Copy
    create temp table stage (like target); insert into stage select * from source where source.filter = 'filter_expression';
  2. Use an inner join with the staging table to delete the rows from the target table that are being updated.

    Put the delete and insert operations in a single transaction block so that if there is a problem, everything will be rolled back.

    Copy
    begin transaction; delete from target using stage where target.primarykey = stage.primarykey;
  3. Insert all of the rows from the staging table.

    Copy
    insert into target select * from stage; end transaction;
  4. Drop the staging table.

    Copy
    drop table stage;