本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
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 | +-------+----------+-----------+--------------------------------------------+
根據值的範圍更新資料表
根據CATGROUP欄中的值範圍更新CATID資料行。
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 | +-------+----------+----------+---------------------------+
根據目前值更新資料表
根據目前的CATGROUP值更新CATNAME和CATDESC欄:
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句子查詢的結果更新資料表
根據CATEGORY子WHERE句中子查詢的結果更新資料表:
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 | +-------+----------+---------+---------------------------------+
根據聯結條件的結果更新資料表
根據CATEGORY表中的匹配行更新表中的原始 11 CATID 行:EVENT
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 | +-------+----------+-----------+--------------------------------------------+
請注意,資EVENT料表會列在FROM子句中,而且在子句中定義了目標資料表的聯結WHERE條件。只有四個資料列符合更新資格。這四個資料列是原本CATID值為 6、7、8 和 9 的資料列;EVENT表格中只會顯示這四個類別:
SELECT DISTINCT catid FROM event;
+-------+ | catid | +-------+ | 6 | | 7 | | 8 | | 9 | +-------+
延伸上一個範例並將其他條件新增至WHERE子句,以更新資料CATEGORY表中原始的 11 列。由於資料行的限制,只有一個資料列符合更新資格 (雖然有四個資料列符合聯結)。CATGROUP
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子句中具有外部聯結的更新
上一個範例顯示陳述式FROM子句中指定的內部聯UPDATE結。下列範例會傳回錯誤,因為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子句中另一個資料表的資料行進行更新
若要更新 listing TICKIT範例資料庫中的表格,其值來自 sales 表中,使用下面的例子。
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 | +--------+------------+