如何在 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 命令對於在資料初始擷取到資料庫後管理資料至關重要。