如何使用 SQLite 更新現有資料

簡介

一旦資料進入資料庫，在表格中存在期間，它不太可能保持靜態。資料會更新以反映其所代表系統的變更，以保持相關性和最新狀態。SQLite 允許您使用 UPDATE SQL 命令變更記錄中的值。

UPDATE 的功能類似於 INSERT（在於您指定欄位及其所需的值）和 DELETE（在於您提供鎖定特定記錄所需的條件）。您也可以逐一或批量修改資料。在本文中，我們將深入探討如何有效地使用 UPDATE 來管理已儲存在表格中的資料。

使用 UPDATE 修改資料

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

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

基本結構包含三個獨立的子句

• 指定要操作的表格
• 提供您想要更新的欄位以及它們的新值
• 定義 SQLite 需要評估以確定要比對哪些記錄的任何條件

雖然您可以像上面那樣直接將值指派給欄位，但您也可以使用欄位列表語法，這在 INSERT 命令中經常看到。

例如，我們可以修改上面的範例，使其如下所示

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

傳回 UPDATE 命令修改的記錄

預設情況下，SQLite 不會顯示受 UPDATE 語句影響的列數。但是，SQLite 在 3.35.0 版本中新增了仿效 PostgreSQL 的 RETURNING 子句。此子句會導致命令傳回已修改記錄的全部或部分內容。

您可以使用星號 * 符號傳回修改列的所有欄位，就像 SELECT 語句一樣

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

此外，您也可以指定您關心顯示的確切欄位，可以使用或不使用別名 AS

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

根據另一個表格中的值更新記錄

根據新的外部資料更新資料是一個相對簡化的過程。您只需要提供表格、欄位、新值和目標條件。

但是，使用 SQLite，您也可以使用 UPDATE 根據資料庫中另一個表格中的資訊有條件地更新表格值。基本語法如下所示

UPDATE table1
SET table1.column1 =(
    SELECT table2.column1
    FROM table2
    WHERE table1.column2 = table2.column2);

在這裡，我們直接更新 table1 中 column1 的值，使其成為 table2 上 SELECT 子查詢的傳回值，但僅在 table1 的 column2 與 table2 的 column2 比對時才更新。FROM 子句表示兩個表格之間的連線，而 WHERE 指定條件。

舉例來說，假設我們有兩個名為 book 和 author 的表格。

CREATE TABLE author (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  last_publication TEXT
);

CREATE TABLE book (
    id INTEGER PRIMARY KEY,
    author_id INT REFERENCES author.id
    title TEXT,
    publication_year INTEGER
);

INSERT INTO author (first_name, last_name)
VALUES
        ('Leo', 'Tolstoy'),
        ('James', 'Joyce'),
        ('Jean-Paul', 'Sarte');

INSERT INTO book (author_id, title, publication_year)
VALUES
        (1, 'Anna Karenina', '1877'),
        (1, 'War and Peace', '1867'),
        (2, 'Ulysses', '1920'),
        (2, 'Dubliners', '1914'),
        (3, 'Nausea', '1938');

這兩個表格與參考 author.id 的 book.author_id 具有關聯性。目前，author 表格的 last_publication 為 NULL。我們可以使用 FROM 和 WHERE 子句將這兩個表格結合在一起，以作者在 book 表格中最新出版的書籍來填入它。

在這裡，我們顯示一個更新 last_publication 的範例

UPDATE author
SET last_publication=(
  SELECT title
  FROM book
  WHERE author_id = author.id
  ORDER BY author_id, publication_year DESC);

如果您現在查詢 author 表格，它將顯示資料庫中他們最新出版的書名

SELECT * FROM author;

+------------+------------+-----------+--------------------------+
      id       first_name  last_name      last_publication     
+-------------+------------+-----------+--------------------------+
       1       Leo          Tolstoy        Anna Karenina       
       2       James         Joyce             Ulysses         
       3       Jean-Paul     Sarte             Nausea          
+-------------+------------+-----------+--------------------------+

結論

在本指南中，我們瞭解了您可以使用 UPDATE 命令修改表格中現有資料的基本方法。執行這些基本概念可讓您指定必要的確切條件，以識別表格中的現有列、使用值更新欄位名稱，並選擇性地傳回受 RETURNING 影響的列。UPDATE 命令對於在初始插入資料庫後管理資料至關重要。

常見問題

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

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        color = 'blue';

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