合并示例

以下示例将执行一个合并以更新 SALES 表。第一个示例使用了较简单的方法：从目标表中删除所有行，然后插入暂存表中的所有行。第二个示例需要更新目标表中的选定列，因此它包含一个额外的更新步骤。

合并示例使用名为 TICKIT 数据集的 Amazon Redshift 示例数据集。作为先决条件，您可以按照开始使用常见数据库任务指南中提供的说明设置 TICKIT 表和数据。有关示例数据集的更多详细信息可在示例数据库中找到。

合并数据来源示例

本节中的示例需要同时包含更新和插入的样本数据来源。例如，我们将创建一个名为 SALES_UPDATE 的示例表，该表使用 SALES 表中的数据。我们将使用表示 12 月的新销售活动的随机数据填充新表。我们将使用 SALES_UPDATE 示例表在后面的示例中创建暂存表。

-- Create a sample table as a copy of the SALES table.

create table tickit.sales_update as
select * from tickit.sales;

-- Change every fifth row to have updates.

update tickit.sales_update
set qtysold = qtysold*2,
pricepaid = pricepaid*0.8,
commission = commission*1.1
where saletime > '2008-11-30'
and mod(sellerid, 5) = 0;

-- Add some new rows to have inserts.
-- This example creates a duplicate of every fourth row.

insert into tickit.sales_update
select (salesid + 172456) as salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, getdate() as saletime
from tickit.sales_update
where saletime > '2008-11-30'
and mod(sellerid, 4) = 0;

基于匹配键替换现有行的合并示例

以下脚本使用 SALES_UPDATE 表对包含 12 月销售活动的新数据的 SALES 表执行合并操作。此示例替换 SALES 表中具有更新的行。对于此示例，我们将更新 qtysold 和 pricepaid 列，但让 commission 和 saletime 保持不变。

MERGE into tickit.sales 
USING tickit.sales_update sales_update  
on ( sales.salesid = sales_update.salesid
and sales.listid = sales_update.listid
and sales_update.saletime > '2008-11-30'
and (sales.qtysold != sales_update.qtysold 
or sales.pricepaid != sales_update.pricepaid))
WHEN MATCHED THEN
update SET qtysold = sales_update.qtysold,
pricepaid = sales_update.pricepaid
WHEN NOT MATCHED THEN 
INSERT (salesid, listid, sellerid, buyerid, eventid, dateid, qtysold , pricepaid, commission, saletime)
values (sales_update.salesid, sales_update.listid, sales_update.sellerid, sales_update.buyerid, sales_update.eventid, 
sales_update.dateid, sales_update.qtysold , sales_update.pricepaid, sales_update.commission, sales_update.saletime);

-- Drop the staging table.
drop table tickit.sales_update;

-- Test to see that commission and salestime were not impacted.
SELECT sales.salesid, sales.commission, sales.salestime, sales_update.commission, sales_update.salestime 
FROM tickit.sales 
INNER JOIN tickit.sales_update sales_update  
ON 
sales.salesid = sales_update.salesid
AND sales.listid = sales_update.listid
AND sales_update.saletime > '2008-11-30'
AND (sales.commission != sales_update.commission 
OR sales.salestime != sales_update.salestime);

在不使用 MERGE 的情况下指定列列表的合并示例

以下示例将执行合并操作以使用 12 月销售活动的新数据更新 SALES。我们需要同时包含更新和插入的样本数据，以及未更改的行。在此示例中，我们希望更新 QTYSOLD 和 PRICEPAID 列，但让 COMMISSION 和 SALETIME 保持不变。以下脚本使用 SALES_UPDATE 表来对 SALES 表执行合并操作。

-- Create a staging table and populate it with rows from SALES_UPDATE for Dec
create temp table stagesales as select * from sales_update
where saletime > '2008-11-30';

-- Start a new transaction
begin transaction;

-- Update the target table using an inner join with the staging table
-- The join includes a redundant predicate to collocate on the distribution key –- A filter on saletime enables a range-restricted scan on SALES

update sales
set qtysold = stagesales.qtysold,
pricepaid = stagesales.pricepaid
from stagesales
where sales.salesid = stagesales.salesid
and sales.listid = stagesales.listid
and stagesales.saletime > '2008-11-30'
and (sales.qtysold != stagesales.qtysold 
or sales.pricepaid != stagesales.pricepaid);
 
-- Delete matching rows from the staging table 
-- using an inner join with the target table

delete from stagesales
using sales
where sales.salesid = stagesales.salesid
and sales.listid = stagesales.listid;

-- Insert the remaining rows from the staging table into the target table
insert into sales
select * from stagesales;

-- End transaction and commit
end transaction;

-- Drop the staging table
drop table stagesales;