Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

合并示例

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

合并数据源示例

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

Copy
-- Create a sample table as a copy of the SALES table create table sales_update as select * from sales; -- Change every fifth row so we have updates update 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 so we have insert examples -- This example creates a duplicate of every fourth row insert into sales_update select (salesid + 172456) as salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, getdate() as saletime from sales_update where saletime > '2008-11-30' and mod(sellerid, 4) = 0;

替换现有行的合并的示例

以下脚本使用 SALES_UPDATE 表对包含 12 月销售活动的新数据的 SALES 表执行合并操作。此示例将删除 SALES 表中具有更新的行,以便让这些行可以替换为暂存表中的已更新行。暂存表只应包含将参与合并的行,因此 CREATE TABLE 语句包含用于排除未更改的行的筛选器。

Copy
-- Create a staging table and populate it with updated rows from SALES_UPDATE create temp table stagesales as select * from sales_update where sales_update.saletime > '2008-11-30' and sales_update.salesid = (select sales.salesid from sales where sales.salesid = sales_update.salesid and sales.listid = sales_update.listid and (sales_update.qtysold != sales.qtysold or sales_update.pricepaid != sales.pricepaid)); -- Start a new transaction begin transaction; -- Delete any rows from SALES that exist in STAGESALES, because they are updates -- The join includes a redundant predicate to collocate on the distribution key –- A filter on saletime enables a range-restricted scan on SALES delete from sales using stagesales where sales.salesid = stagesales.salesid and sales.listid = stagesales.listid and sales.saletime > '2008-11-30'; -- Insert all the 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;

指定列列表的合并的示例

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

Copy
-- 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;