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

这两个表之间存在关联，其中 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 命令对于在数据首次进入数据库后管理数据至关重要。