如何在 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');

这两个表与引用 director.id 的 film.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 命令对于在数据初始导入数据库后管理数据至关重要。