UPDATE 语句的示例 - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

UPDATE 语句的示例

有关以下示例中使用的表的更多信息,请参阅示例数据库

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

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

根据一系列值更新表

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

UPDATE category SET catgroup='Theatre' WHERE catid BETWEEN 6 AND 8; 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 | +-------+----------+----------+---------------------------+

根据当前值更新表

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

UPDATE category SET catdesc=default, catname='Shows' WHERE catgroup='Theatre'; SELECT * FROM category WHERE catname='Shows'; +-------+----------+---------+---------+ | catid | catgroup | catname | catdesc | +-------+----------+---------+---------+ | 6 | Theatre | Shows | NULL | | 7 | Theatre | Shows | NULL | | 8 | Theatre | Shows | NULL | +-------+----------+---------+---------+)

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

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

TRUNCATE category; COPY category FROM 's3://redshift-downloads/tickit/category_pipe.txt' DELIMITER '|' IGNOREHEADER 1 REGION 'us-east-1' IAM_ROLE default;

根据 WHERE 子句子查询结果更新表

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

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');

查看更新后的表:

SELECT * FROM category ORDER BY catid; +-------+----------+-----------+--------------------------------------------+ | catid | catgroup | catname | catdesc | +-------+----------+-----------+--------------------------------------------+ | 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 | +-------+----------+-----------+--------------------------------------------+

根据 WITH 子句子查询结果更新表

要使用 WITH 子句根据子查询的结果更新 CATEGORY 表,请使用以下示例。

WITH u1 as (SELECT catid FROM event ORDER BY catid DESC LIMIT 1) UPDATE category SET catid='200' FROM u1 WHERE u1.catid=category.catid; SELECT * FROM category ORDER BY catid DESC LIMIT 1; +-------+----------+---------+---------------------------------+ | catid | catgroup | catname | catdesc | +-------+----------+---------+---------------------------------+ | 200 | Concerts | Pop | All rock and pop music concerts | +-------+----------+---------+---------------------------------+

根据联接条件结果更新表

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

UPDATE category SET catid=100 FROM event WHERE event.catid=category.catid; SELECT * FROM category ORDER BY catid; +-------+----------+-----------+--------------------------------------------+ | catid | catgroup | catname | catdesc | +-------+----------+-----------+--------------------------------------------+ | 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 | Concerts | Pop | All rock and pop music concerts | | 100 | Shows | Plays | All non-musical theatre | | 100 | Shows | Opera | All opera and light opera | | 100 | Shows | Musicals | Broadway Musical | +-------+----------+-----------+--------------------------------------------+

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

SELECT DISTINCT catid FROM event; +-------+ | catid | +-------+ | 6 | | 7 | | 8 | | 9 | +-------+

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

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 | +-------+----------+---------+---------------------------------+

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

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 子句不支持与目标表的外部联接:

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 语句需要外部联接,您可以将外部联接语法移到子查询中:

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

在 SET 子句中使用另一个表中的列进行更新

要使用 sales 表中的值更新 TICKIT 示例数据库中的 listing 表,请使用以下示例。

SELECT listid, numtickets FROM listing WHERE sellerid = 1 ORDER BY 1 ASC LIMIT 5; +--------+------------+ | listid | numtickets | +--------+------------+ | 100423 | 4 | | 108334 | 24 | | 117150 | 4 | | 135915 | 20 | | 205927 | 6 | +--------+------------+ UPDATE listing SET numtickets = sales.sellerid FROM sales WHERE sales.sellerid = 1 AND listing.sellerid = sales.sellerid; SELECT listid, numtickets FROM listing WHERE sellerid = 1 ORDER BY 1 ASC LIMIT 5; +--------+------------+ | listid | numtickets | +--------+------------+ | 100423 | 1 | | 108334 | 1 | | 117150 | 1 | | 135915 | 1 | | 205927 | 1 | +--------+------------+