分享至

简介

一旦数据进入数据库,它在表中停留期间很可能不会保持静态。数据会更新以反映它们所代表的系统中的变化,以保持相关性和最新状态。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 版本中添加了模仿 PostgreSQLRETURNING 子句。此子句使命令返回已修改记录的全部或部分。

你可以使用星号 * 符号来返回所有已修改行的列,非常类似于 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);

在这里,我们直接将 table1column1 的值更新为 table2SELECT 子查询的返回值,但仅在 table1column2table2column2 匹配的行中。 FROM 子句表示两个表之间的连接,WHERE 指定条件。

例如,假设我们有两个表,名为 bookauthor

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

这两个表通过 book.author_id 引用 author.id 建立关联。目前,author 表的 last_publicationNULL。我们可以使用 FROMWHERE 子句将这两个表连接起来,用作者在 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 语句在 SQLite 中编辑记录。

基本语法如下:

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

在 SQLite 中,你可以使用 UPDATE 命令,其形式大致如下:

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

此语法允许你根据 WHERE 条件进行批量更新。在此示例中,对于 color 列为 'blue' 的任何记录,其列都会被更新。

默认情况下,SQLite 不显示受 UPDATE 语句影响的行数。

然而,SQLite 确实有 RETURNING 子句来返回已修改记录的全部或部分。

基本语法如下,其中 * 表示返回所有列名:

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

在 SQLite 中,没有像许多其他关系型数据库那样的 IF EXISTS 子句。

要控制现有数据的 INSERTUPDATE,你需要在语句中添加一个 ON CONFLICT 子句

要在 SQLite 中执行带有 JOIN 的 UPDATE,你可以使用 UPDATE FROM 扩展。这个 SQL 扩展允许 UPDATE 语句由数据库中的其他表驱动。

使用 UPDATE-FROM,你可以将目标表与数据库中的其他表连接起来,以帮助计算哪些行需要更新以及这些行应该有哪些新值。

作者
Alex Emerich

Alex Emerich

Alex 是一个典型的观鸟者、嘻哈爱好者和书呆子,他也喜欢撰写关于数据库的文章。他目前居住在柏林,在那里可以看到他像利奥波德·布鲁姆(Leopold Bloom)一样漫无目的地在城市中行走。
© . All rights reserved.