PostgreSQL UPDATE / PostgreSQL更新数据 / PostgreSQL UPDATE / PostgreSQL更新数据

PostgreSQL UPDATE / PostgreSQL更新数据

Overview / 概述

The UPDATE statement modifies existing records in a table. This chapter covers how to update single or multiple columns and use conditions.

UPDATE语句修改表中的现有记录。本章介绍如何更新单列或多列以及使用条件。

Basic UPDATE / 基本更新

Update Single Column / 更新单列

UPDATE users SET status = 'active' WHERE id = 1;

Update Multiple Columns / 更新多列

UPDATE users SET 
    first_name = 'John',
    last_name = 'Doe',
    email = 'john.doe@example.com'
WHERE id = 1;

UPDATE with WHERE / 带条件的更新

-- Update specific row
UPDATE users SET status = 'inactive' WHERE id = 5;

-- Update multiple rows
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

-- Update with IN
UPDATE users SET status = 'vip' WHERE id IN (1, 2, 3, 4, 5);

UPDATE with RETURNING / 返回更新结果

UPDATE users SET last_login = NOW() WHERE id = 1
RETURNING id, name, last_login;

UPDATE with Subquery / 使用子查询更新

-- Update based on subquery
UPDATE products SET price = (
    SELECT AVG(price) FROM products WHERE category_id = products.category_id
) WHERE price IS NULL;

Summary / 小结

UPDATE statement:

• Modify existing records
• Use WHERE to filter rows
• Update single or multiple columns
• RETURNING to get updated values