本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

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

根據目前值更新資料表

根據目前的 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 子句子查詢的結果更新資料表

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

請注意，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 子句中外部聯結的更新

上面的範例說明了 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          |
+--------+------------+