UPDATE 陳述式的範例 - Amazon Redshift

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

UPDATE 陳述式的範例

如需下列範例中所用資料表的詳細資訊,請參閱 範本資料庫

TICKIT 資料庫中的 CATEGORY 資料表包含以下資料列:

catid | catgroup | catname | catdesc -------+----------+-----------+----------------------------------------- 1 | Sports | MLB | Major League Baseball 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 | Musical theatre 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 (11 rows)

根據值的範圍更新資料表

根據 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 (3 rows)

根據目前值更新資料表

根據目前的 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 | 7 | Theatre | Shows | 8 | Theatre | Shows | (3 rows)

在此情況下,CATDESC 資料欄已設為 null,因為資料表建立時並未定義任何預設值。

執行下列命令,將 CATEGORY 資料表資料重設回原始值:

truncate category; copy category from 's3://mybucket/data/category_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|';

根據 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 1; catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 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 (11 rows)

根據聯結條件的結果更新資料表

根據 EVENT 資料表中相符的 CATID 資料列,更新 CATEGORY 資料表中原有的 11 個資料列:

update category set catid=100 from event where event.catid=category.catid; select * from category order by 1; catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 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 | Shows | Opera | All opera and light opera 100 | Shows | Musicals | Musical theatre 100 | Concerts | Pop | All rock and pop music concerts 100 | Shows | Plays | All non-musical theatre (11 rows)

請注意,EVENT 會在 FROM 子句中列出,而目標資料表的聯結條件會在 WHERE 子句中定義。只有四個資料列符合更新資格。這四個資料列是 CATID 值原本為 6、7、8 和 9 的資料列;EVENT 資料表中只會表示這四種類別:

select distinct catid from event; catid ------- 9 8 6 7 (4 rows)

藉由延伸先前的範例並新增其他條件至 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 (1 row)

撰寫此範例的替代方法如下:

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';