从补丁 198 开始，Amazon Redshift 将不再支持创建新的 Python UDF。现有的 Python UDF 将继续正常运行至 2026 年 6 月 30 日。有关更多信息，请参阅[博客文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。 # UPDATE 语句的示例 有关以下示例中使用的表的更多信息，请参阅[示例数据库](c_sampledb.md)。 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 | +--------+------------+ ```