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

EVENT テーブルは FROM 句にリストされ、ターゲットテーブルへの結合条件は WHERE 句で定義されていることに注意してください。更新用に限定される行は 4 行だけです。これらの 4 行は、CATID 値が元々 6、7、8、および 9 だった行です。この 4 つのカテゴリだけが、EVENT テーブル内で表現されます。

SELECT DISTINCT catid FROM event;

+-------+
| catid |
+-------+
| 6     |
| 7     |
| 8     |
| 9     |
+-------+

前の例を拡張して、WHERE 句に別の条件を追加することで、CATEGORY テーブル内の元の 11 行を更新します。CATGROUP 列に関する制限により、更新用に限定される行は 1 行だけです (ただし、結合用には 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 |
+-------+----------+---------+---------------------------------+

この例を作成するためのもう 1 つの方法を次に示します。

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 句内の別のテーブルの列を使用した更新

TICKIT サンプルデータベース内の listing テーブルを 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          |
+--------+------------+