如何在PostgreSQL中更新現有資料

簡介

儲存在資料庫中的記錄通常不是靜態的。它們必須更新以反映所代表系統中的變化，從而保持相關性。PostgreSQL允許您使用 UPDATE SQL命令更改記錄中的值。

在許多方面，UPDATE 的功能類似於 INSERT (您需要指定列及其所需的值) 和 DELETE (您需要提供定位特定記錄所需的標準)。您可以逐個或批次修改表中任何列的資料。在本指南中，我們將探討如何有效地使用此命令來管理已在表中的資料。

使用 UPDATE 修改資料

UPDATE 命令的基本語法如下所示

UPDATE my_table
SET
    column1 = value1,
    column2 = value2
WHERE
    id = 1;

如上所示，基本結構包含三個獨立子句

• 指定要操作的表，
• 提供您希望更新的列及其新值，以及
• 定義PostgreSQL需要評估以確定匹配哪些記錄的任何條件

在上述基本模板中，我們演示了直接為列賦值的樣式。您也可以使用列列表語法，這在 INSERT 命令中很常見。

例如，上面的示例也可以這樣指定

UPDATE my_table
SET (column1, column2) =
    (value1, value2)
WHERE
    id = 1;

成功提交後，PostgreSQL透過輸出操作名稱和受影響的行數來確認該操作

UPDATE <count>

返回由 UPDATE 命令修改的記錄

與許多其他命令一樣，PostgreSQL允許您在 UPDATE 命令後附加一個 RETURNING 子句。這會使命令返回所有或部分被修改的記錄。

您可以使用星號 * 符號來返回被修改行的所有列

UPDATE my_table
SET
    column1 = value1,
    column2 = value2
WHERE
    id = 1
RETURNING *;

或者，您可以指定您關心的確切列，以僅顯示特定屬性

UPDATE my_table
SET
    column1 = value1,
    column2 = value2
WHERE
    id = 1
RETURNING column1 AS 'first column';

在這裡，我們還使用了列別名來設定輸出中列標題的標籤。

基於另一個表中的值更新記錄

基於提供新的外部資料進行的更新相對簡單。您只需提供表、列、新值和目標條件即可。

但是，您也可以使用 UPDATE 根據連線表中儲存的資訊有條件地更新表值。基本語法如下所示

UPDATE table1
SET table1.column1 = <some_value>
FROM table2
WHERE table1.column2 = table2.column2;

在這裡，我們將 table1 表中 column1 的值更新為 <some_value>，但僅限於 table1 的 column2 與 table2 的 column2 匹配的行。FROM 子句表示兩個表之間的連線，而 WHERE 構造則指定連線條件。

舉個例子，假設我們有兩個表，分別叫做 film 和 director。

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    latest_film TEXT
);

CREATE TABLE film (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    director_id INT REFERENCES director(id),
    release_date DATE NOT NULL
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

INSERT INTO film (title, director_id, release_date)
VALUES
    ('first movie', 1, '2010-08-24'),
    ('second movie', 1, '2010-12-15'),
    ('third movie', 2, '2011-01-01'),
    ('fourth movie', 2, '2012-08-02');

這兩個表之間存在關聯，其中 film.director_id 引用 director.id。目前，director 表中的 latest_film 為 NULL。但是，我們可以使用 FROM 和 WHERE 子句將兩個表連線起來，並用導演的最新電影標題填充它。

在這裡，我們使用 WITH 子句建立一個名為 latest_films 的公共表表達式（CTE），以便在我們的 UPDATE 語句中引用它

WITH latest_films AS (
    SELECT DISTINCT ON (director_id)
        *
    FROM
        film
    ORDER BY
        director_id,
        release_date DESC)
UPDATE director set latest_film = title FROM latest_films
WHERE director.id = latest_films.director_id;

如果您查詢 director 表，現在應該會顯示每位導演的最新電影

SELECT * FROM director;

 id | name  | latest_film
----+-------+--------------
  3 | sue   |
  1 | frank | second movie
  2 | bob   | fourth movie
(3 rows)

總結

在本指南中，我們探討了使用 UPDATE 命令修改表中現有資料的基本方法。透過這些基本概念，您可以指定識別表中現有行的確切條件，用新值更新列名，並可選擇返回受影響的行。UPDATE 命令對於在資料首次進入資料庫後管理資料至關重要。