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

UPDATE 语句的示例

TICKIT 数据库中的 CATEGORY 表包含以下行:

Copy
catid | catgroup | catname | catdesc -------+----------+-----------+----------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts (11 rows)

基于一系列值更新表

基于 CATID 列中的一系列值更新 CATGROUP 列。

Copy
update category set catgroup='Theatre' where catid between 6 and 8;
Copy
select * from category where catid between 6 and 8; catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 6 | Theatre | Musicals | Musical theatre 7 | Theatre | Plays | All non-musical theatre 8 | Theatre | Opera | All opera and light opera (3 rows)

基于当前值更新表

基于 CATNAME 和 CATDESC 列的当前 CATGROUP 值更新这两个列:

Copy
update category set catdesc=default, catname='Shows' where catgroup='Theatre';
Copy
select * from category where catname='Shows'; catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 6 | Theatre | Shows | 7 | Theatre | Shows | 8 | Theatre | Shows | (3 rows)

在本示例中,CATDESC 列已设置为 null,因为创建表时未定义默认值。

运行以下命令可将 CATEGORY 表数据设置回原始值:

Copy
truncate category; copy category from 's3://mybucket/data/category_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|';

基于 WHERE 子句子查询的结果更新表

基于 WHERE 子句中子查询的结果更新 CATEGORY 表:

Copy
update category set catdesc='Broadway Musical' where category.catid in (select category.catid from category join event on category.catid = event.catid join venue on venue.venueid = event.venueid join sales on sales.eventid = event.eventid where venuecity='New York City' and catname='Musicals');

查看更新后的表:

Copy
select * from category order by 1; catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Broadway Musical 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts (11 rows)

基于联接条件的结果更新表

基于 EVENT 表中匹配的 CATID 行更新 CATEGORY 表中的原始 11 个行:

Copy
update category set catid=100 from event where event.catid=category.catid; select * from category order by 1; catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts 100 | Shows | Opera | All opera and light opera 100 | Shows | Musicals | Musical theatre 100 | Concerts | Pop | All rock and pop music concerts 100 | Shows | Plays | All non-musical theatre (11 rows)

请注意,在 FROM 子句中列出 EVENT 表,并且在 WHERE 子句中定义目标表的联接条件。只有 4 行符合更新条件。对于这 4 行,其原始 CATID 值为 6、7、8 和 9;EVENT 表中仅呈现这 4 个类别:

Copy
select distinct catid from event; catid ------- 9 8 6 7 (4 rows)

通过扩展上一个示例并向 WHERE 子句添加其他条件来更新 CATEGORY 表中的原始 11 个行。由于 CATGROUP 列的限制,只有一行符合更新条件(虽然这 4 行都符合联接条件)。

Copy
update category set catid=100 from event where event.catid=category.catid and catgroup='Concerts'; select * from category where catid=100; catid | catgroup | catname | catdesc -------+----------+---------+--------------------------------- 100 | Concerts | Pop | All rock and pop music concerts (1 row)

编写此示例的另一种方法如下:

Copy
update category set catid=100 from event join category cat on event.catid=cat.catid where cat.catgroup='Concerts';

这种方法的好处是,明确地将联接条件与限定要更新的行的任何其他条件分隔开。请注意,在 FROM 子句中使用了 CATEGORY 表的别名 CAT。

在 FROM 子句中使用外部联接进行更新

上一个示例显示了 UPDATE 语句的 FROM 子句中指定的内部联接。以下示例返回一个错误,因为 FROM 子句不支持与目标表的外部联接:

Copy
update category set catid=100 from event left join category cat on event.catid=cat.catid where cat.catgroup='Concerts'; ERROR: Target table must be part of an equijoin predicate

如果 UPDATE 语句需要外部联接,您可以将外部联接语法移到子查询中:

Copy
update category set catid=100 from (select event.catid from event left join category cat on event.catid=cat.catid) eventcat where category.catid=eventcat.catid and catgroup='Concerts';