Contoh UPDATE pernyataan

Untuk informasi selengkapnya tentang tabel yang digunakan dalam contoh berikut, lihatDatabase sampel.

CATEGORYTabel dalam TICKIT database berisi baris berikut:

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

Memperbarui tabel berdasarkan rentang nilai

Perbarui CATGROUP kolom berdasarkan rentang nilai di CATID kolom.

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

Memperbarui tabel berdasarkan nilai saat ini

Perbarui CATDESC kolom CATNAME dan berdasarkan CATGROUP nilainya saat ini:

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

Dalam hal ini, CATDESC kolom disetel ke null karena tidak ada nilai default yang ditentukan saat tabel dibuat.

Jalankan perintah berikut untuk mengatur data CATEGORY tabel kembali ke nilai asli:

TRUNCATE category;

COPY category
FROM 's3://redshift-downloads/tickit/category_pipe.txt' 
DELIMITER '|' 
IGNOREHEADER 1 
REGION 'us-east-1'
IAM_ROLE default;

Memperbarui tabel berdasarkan hasil subquery WHERE klausa

Memperbarui CATEGORY tabel berdasarkan hasil subquery dalam WHERE klausa:

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

Lihat tabel yang diperbarui:

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

Memperbarui tabel berdasarkan hasil subquery WITH klausa

Untuk memperbarui CATEGORY tabel berdasarkan hasil subquery menggunakan WITH klausa, gunakan contoh berikut.

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

Memperbarui tabel berdasarkan hasil dari kondisi gabungan

Perbarui 11 baris asli dalam CATEGORY tabel berdasarkan CATID baris yang cocok dalam EVENT tabel:

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

Perhatikan bahwa EVENT tabel tercantum dalam FROM klausa dan kondisi gabungan ke tabel target didefinisikan dalam WHERE klausa. Hanya empat baris yang memenuhi syarat untuk pembaruan. Keempat baris ini adalah baris yang CATID nilainya awalnya 6, 7, 8, dan 9; hanya empat kategori yang diwakili dalam EVENT tabel:

SELECT DISTINCT catid FROM event;

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

Perbarui 11 baris asli dalam CATEGORY tabel dengan memperluas contoh sebelumnya dan menambahkan kondisi lain ke WHERE klausa. Karena pembatasan pada CATGROUP kolom, hanya satu baris yang memenuhi syarat untuk pembaruan (meskipun empat baris memenuhi syarat untuk bergabung).

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

Cara alternatif untuk menulis contoh ini adalah sebagai berikut:

UPDATE category SET catid=100
FROM event JOIN category cat ON event.catid=cat.catid
WHERE cat.catgroup='Concerts';

Keuntungan dari pendekatan ini adalah bahwa kriteria gabungan jelas dipisahkan dari kriteria lain yang memenuhi syarat baris untuk pembaruan. Perhatikan penggunaan alias CAT untuk CATEGORY tabel dalam FROM klausa.

Pembaruan dengan gabungan luar dalam FROM klausa

Contoh sebelumnya menunjukkan gabungan batin yang ditentukan dalam FROM klausa UPDATE pernyataan. Contoh berikut mengembalikan kesalahan karena FROM klausa tidak mendukung gabungan luar ke tabel target:

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

Jika gabungan luar diperlukan untuk UPDATE pernyataan, Anda dapat memindahkan sintaks gabungan luar ke subquery:

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

Pembaruan dengan kolom dari tabel lain dalam SET klausa

Untuk memperbarui listing tabel dalam database TICKIT sampel dengan nilai-nilai dari sales tabel, gunakan contoh berikut.

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