如何使用 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 *;