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