如何在 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 允许您将 RETURNING 子句附加到 UPDATE 命令。这会导致命令返回所有或部分修改的记录。

您可以使用星号 * 符号来返回所有修改行的所有列

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 命令对于在数据初始加载到数据库后进行管理至关重要。