PostgreSQL UPDATE 语句

最后修改于 2025 年 2 月 25 日

PostgreSQL UPDATE 语句用于修改表中的现有行。它允许您更改一行或多行的一个或多个列的值。本教程通过实际示例介绍如何使用 UPDATE 语句。

UPDATE 语句可以与 WHERE 子句一起使用来过滤要更新的行，也可以不带 WHERE 子句来更新表中的所有行。重要的是要谨慎使用此语句，因为它会永久修改数据。

基本 UPDATE 语句

此示例演示如何更新 books 表中的单行

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

UPDATE books
SET price = 14.99
WHERE book_id = 101;

UPDATE 语句指定表，SET 子句定义新值，WHERE 子句标识要更新的行。

更新多列

此示例演示如何在一行中更新多列

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

UPDATE books
SET price = 16.50, publication_year = 2023
WHERE book_id = 102;

SET 子句更新指定行的 price 和 publication_year 列。

更新所有行

此示例演示如何更新 books 表中的所有行

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

UPDATE books
SET price = price * 1.1;

不带 WHERE 子句的 UPDATE 语句会更新表中的所有行。在此示例中，所有图书的价格都提高了 10%。

使用子查询更新

此示例演示如何使用子查询更新行

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

UPDATE books
SET price = price * 1.2
WHERE book_id IN (
    SELECT book_id FROM books
    WHERE publication_year < 2000
);

子查询根据 publication_year 列标识要更新的行。

使用 JOIN 更新

此示例演示如何使用 JOIN 更新行

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author_id INTEGER REFERENCES authors(author_id),
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

-- CREATE TABLE authors (
--     author_id INTEGER PRIMARY KEY,
--     name VARCHAR(100) NOT NULL
-- );

UPDATE books
SET price = price * 1.15
FROM authors
WHERE books.author_id = authors.author_id
AND authors.name = 'Jane Doe';

FROM 子句连接 books 和 authors 表，WHERE 子句过滤要更新的行。

使用 RETURNING 子句更新

此示例演示如何更新行并返回更新后的数据

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

UPDATE books
SET price = price * 1.1
WHERE genre = 'Sci-Fi'
RETURNING *;

RETURNING 子句返回更新后的行，这对于验证或进一步处理很有用。

使用 CASE 语句更新

此示例演示如何使用 CASE 语句有条件地更新行

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

UPDATE books
SET price = CASE
    WHEN publication_year < 2000 THEN price * 1.2
    WHEN publication_year BETWEEN 2000 AND 2010 THEN price * 1.1
    ELSE price * 1.05
END;

CASE 语句根据 publication_year 列应用不同的更新。

使用 DEFAULT 值更新

此示例演示如何将列更新为其默认值

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL DEFAULT 0.00 CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

UPDATE books
SET price = DEFAULT
WHERE book_id = 103;

DEFAULT 关键字将 price 列设置为其默认值。

使用 CURRENT_TIMESTAMP 更新

此示例演示如何使用当前时间戳更新列

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025),
--     last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- );

UPDATE books
SET last_updated = CURRENT_TIMESTAMP
WHERE book_id = 104;

CURRENT_TIMESTAMP 函数使用当前日期和时间更新 last_updated 列。

使用 UPDATE 的最佳实践

• 小心使用 WHERE 子句： 始终仔细检查 WHERE 子句，以避免更新意外的行。
• 备份数据： 在执行大量更新之前备份数据。
• 使用事务： 将 UPDATE 语句包装在事务中以确保原子性。
• 使用 SELECT 测试： 在更新之前，使用 SELECT 语句测试您的 WHERE 子句。

来源

PostgreSQL 文档

在本文中，我们通过实际示例和最佳实践，探讨了如何使用 PostgreSQL UPDATE 语句修改表中的数据。

作者

我的名字是 Jan Bodnar，我是一名充满热情的程序员，拥有丰富的编程经验。我自 2007 年以来一直在撰写编程文章。迄今为止，我已撰写了 1400 多篇文章和 8 本电子书。我在编程教学方面拥有十多年的经验。