PostgreSQL 数据更新 #
UPDATE 概述 #
UPDATE 语句用于修改表中的现有数据。
text
┌─────────────────────────────────────────────────────────────┐
│ UPDATE 语句结构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ UPDATE table_name │
│ SET column1 = value1, column2 = value2, ... │
│ [ WHERE condition ] │
│ [ RETURNING * | column1, column2, ... ]; │
│ │
│ 组成部分： │
│ ├── UPDATE table_name 指定要更新的表 │
│ ├── SET column = value 指定要更新的列和新值 │
│ ├── WHERE condition 指定更新条件（重要！） │
│ └── RETURNING 返回更新后的数据 │
│ │
└─────────────────────────────────────────────────────────────┘
基本更新 #
更新单列 #
sql
-- 创建示例表
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2),
stock INTEGER DEFAULT 0,
status VARCHAR(20) DEFAULT 'active',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO products (name, price, stock) VALUES
('Product A', 100.00, 50),
('Product B', 200.00, 30),
('Product C', 150.00, 20);
-- 更新单个列
UPDATE products
SET price = 120.00
WHERE id = 1;
-- 不带 WHERE 条件会更新所有行（危险！）
-- UPDATE products SET price = 100.00; -- 更新所有行
更新多列 #
sql
-- 更新多个列
UPDATE products
SET
price = 180.00,
stock = 100,
status = 'featured'
WHERE id = 2;
-- 使用表达式更新
UPDATE products
SET
price = price * 1.1,
updated_at = CURRENT_TIMESTAMP
WHERE id = 3;
使用表达式 #
sql
-- 数值运算
UPDATE products
SET price = price * 1.1 -- 价格上涨 10%
WHERE status = 'active';
-- 字符串操作
UPDATE products
SET name = UPPER(name)
WHERE id = 1;
-- 使用函数
UPDATE products
SET updated_at = CURRENT_TIMESTAMP
WHERE id IN (1, 2, 3);
-- 使用 CASE 表达式
UPDATE products
SET status = CASE
WHEN stock = 0 THEN 'out_of_stock'
WHEN stock < 10 THEN 'low_stock'
ELSE 'in_stock'
END;
条件更新 #
WHERE 条件 #
sql
-- 根据条件更新
UPDATE products
SET status = 'inactive'
WHERE stock = 0;
-- 多条件更新
UPDATE products
SET price = price * 0.9
WHERE status = 'active'
AND stock > 50;
-- 使用 IN
UPDATE products
SET status = 'featured'
WHERE id IN (1, 2, 3);
-- 使用 BETWEEN
UPDATE products
SET price = price * 1.05
WHERE price BETWEEN 100 AND 200;
-- 使用 LIKE
UPDATE products
SET status = 'special'
WHERE name LIKE 'Special%';
-- 使用子查询
UPDATE products
SET status = 'popular'
WHERE id IN (
SELECT product_id
FROM order_items
GROUP BY product_id
HAVING COUNT(*) > 100
);
使用子查询更新 #
sql
-- 从另一个表获取更新值
CREATE TABLE price_updates (
product_id INTEGER,
new_price DECIMAL(10, 2)
);
INSERT INTO price_updates VALUES
(1, 150.00),
(2, 250.00);
-- 使用子查询更新
UPDATE products
SET price = (
SELECT new_price
FROM price_updates
WHERE price_updates.product_id = products.id
)
WHERE id IN (SELECT product_id FROM price_updates);
-- 使用 FROM 子句（PostgreSQL 特有）
UPDATE products
SET price = price_updates.new_price
FROM price_updates
WHERE products.id = price_updates.product_id;
RETURNING 子句 #
sql
-- 返回更新后的所有列
UPDATE products
SET price = 160.00
WHERE id = 1
RETURNING *;
-- 输出：
-- id | name | price | stock | status | updated_at
-- ----+-----------+--------+-------+---------+----------------------------
-- 1 | Product A | 160.00 | 50 | active | 2026-03-29 10:00:00.000000
-- 返回特定列
UPDATE products
SET price = price * 1.1
WHERE id = 2
RETURNING id, name, price;
-- 返回更新前后的值
UPDATE products
SET price = price * 1.1
WHERE id = 3
RETURNING
id,
name,
price AS new_price,
(price / 1.1) AS old_price;
-- 在应用中使用
-- UPDATE products SET stock = stock - $1 WHERE id = $2 RETURNING stock;
FROM 子句更新 #
PostgreSQL 允许使用 FROM 子句从其他表获取数据：
sql
-- 创建订单表
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
status VARCHAR(20) DEFAULT 'pending'
);
INSERT INTO orders (product_id, quantity, status) VALUES
(1, 5, 'pending'),
(2, 3, 'pending'),
(1, 2, 'completed');
-- 从订单表更新库存
UPDATE products
SET stock = stock - orders.quantity
FROM orders
WHERE products.id = orders.product_id
AND orders.status = 'pending';
-- 多表关联更新
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
discount DECIMAL(3, 2)
);
INSERT INTO categories (name, discount) VALUES
('Electronics', 0.10),
('Books', 0.05);
ALTER TABLE products ADD COLUMN category_id INTEGER;
UPDATE products
SET category_id = 1
WHERE id IN (1, 2);
-- 根据分类折扣更新价格
UPDATE products
SET price = price * (1 - categories.discount)
FROM categories
WHERE products.category_id = categories.id;
更新 JSON 数据 #
sql
-- 创建带 JSON 列的表
CREATE TABLE user_settings (
id SERIAL PRIMARY KEY,
user_id INTEGER,
settings JSONB
);
INSERT INTO user_settings (user_id, settings) VALUES
(1, '{"theme": "light", "language": "en", "notifications": true}'),
(2, '{"theme": "dark", "language": "zh", "notifications": false}');
-- 更新整个 JSON 值
UPDATE user_settings
SET settings = '{"theme": "dark", "language": "zh", "notifications": true}'
WHERE user_id = 1;
-- 更新 JSON 中的特定字段
UPDATE user_settings
SET settings = settings || '{"theme": "dark"}'::jsonb
WHERE user_id = 1;
-- 使用 jsonb_set 更新嵌套值
UPDATE user_settings
SET settings = jsonb_set(
settings,
'{preferences,fontSize}',
'"16px"'
)
WHERE user_id = 1;
-- 删除 JSON 字段
UPDATE user_settings
SET settings = settings - 'notifications'
WHERE user_id = 2;
更新数组数据 #
sql
-- 创建带数组列的表
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
tags VARCHAR(50)[]
);
INSERT INTO articles (title, tags) VALUES
('Article 1', ARRAY['tech', 'programming']),
('Article 2', ARRAY['lifestyle', 'travel']);
-- 更新整个数组
UPDATE articles
SET tags = ARRAY['tech', 'programming', 'database']
WHERE id = 1;
-- 添加元素到数组
UPDATE articles
SET tags = array_append(tags, 'featured')
WHERE id = 1;
-- 删除数组元素
UPDATE articles
SET tags = array_remove(tags, 'travel')
WHERE id = 2;
-- 更新数组特定位置
UPDATE articles
SET tags[1] = 'technology'
WHERE id = 1;
批量更新 #
sql
-- 使用 CASE 批量更新不同值
UPDATE products
SET status = CASE id
WHEN 1 THEN 'featured'
WHEN 2 THEN 'sale'
WHEN 3 THEN 'new'
ELSE status
END
WHERE id IN (1, 2, 3);
-- 使用 VALUES 列表批量更新
UPDATE products AS p
SET
price = v.price,
stock = v.stock
FROM (VALUES
(1, 150.00, 60),
(2, 220.00, 40),
(3, 180.00, 25)
) AS v(id, price, stock)
WHERE p.id = v.id;
-- 使用临时表批量更新
CREATE TEMP TABLE product_updates (
product_id INTEGER,
new_price DECIMAL(10, 2),
new_stock INTEGER
);
INSERT INTO product_updates VALUES
(1, 160.00, 55),
(2, 230.00, 35),
(3, 170.00, 22);
UPDATE products
SET
price = pu.new_price,
stock = pu.new_stock
FROM product_updates pu
WHERE products.id = pu.product_id;
性能优化 #
使用索引 #
sql
-- 确保 WHERE 条件使用索引
-- 创建索引
CREATE INDEX idx_products_status ON products(status);
-- 这个更新会使用索引
UPDATE products
SET price = price * 1.1
WHERE status = 'active';
-- 查看执行计划
EXPLAIN UPDATE products
SET price = price * 1.1
WHERE status = 'active';
分批更新 #
sql
-- 大批量更新时分批进行
-- 方法1：使用 LIMIT（需要循环）
UPDATE products
SET status = 'processed'
WHERE status = 'pending'
AND id IN (
SELECT id FROM products
WHERE status = 'pending'
LIMIT 1000
);
-- 方法2：使用 ID 范围
UPDATE products
SET status = 'processed'
WHERE status = 'pending'
AND id BETWEEN 1 AND 10000;
-- 方法3：使用时间范围
UPDATE products
SET status = 'archived'
WHERE created_at < '2025-01-01'
AND id IN (
SELECT id FROM products
WHERE created_at < '2025-01-01'
LIMIT 10000
);
避免全表扫描 #
sql
-- 不推荐：没有 WHERE 条件
UPDATE products SET updated_at = CURRENT_TIMESTAMP;
-- 推荐：有明确的 WHERE 条件
UPDATE products
SET updated_at = CURRENT_TIMESTAMP
WHERE id IN (SELECT id FROM recent_updates);
常见错误 #
违反约束 #
sql
-- 违反唯一约束
-- ERROR: duplicate key value violates unique constraint
UPDATE users SET email = 'existing@example.com' WHERE id = 2;
-- 违反外键约束
-- ERROR: update or delete on table "users" violates foreign key constraint
UPDATE users SET id = 999 WHERE id = 1;
-- 违反检查约束
-- ERROR: new row for relation "products" violates check constraint
UPDATE products SET price = -10 WHERE id = 1;
类型不匹配 #
sql
-- 类型转换错误
-- ERROR: invalid input syntax for type numeric
UPDATE products SET price = 'not_a_number' WHERE id = 1;
-- 正确做法
UPDATE products SET price = '100.00'::DECIMAL WHERE id = 1;
安全更新 #
使用事务 #
sql
-- 使用事务确保安全
BEGIN;
-- 先查询要更新的数据
SELECT * FROM products WHERE status = 'inactive';
-- 确认无误后更新
UPDATE products
SET status = 'archived'
WHERE status = 'inactive';
-- 确认结果
SELECT * FROM products WHERE status = 'archived';
-- 确认无误后提交
COMMIT;
-- 如果有问题，回滚
-- ROLLBACK;
使用 LIMIT #
sql
-- 限制更新行数（PostgreSQL 需要子查询）
UPDATE products
SET status = 'processed'
WHERE id IN (
SELECT id FROM products
WHERE status = 'pending'
LIMIT 100
);
学习路径 #
text
基础阶段
├── 数据库操作
├── 表操作
├── 数据插入
├── 数据更新（本文）
└── 数据删除
下一步 #
掌握了数据更新后，接下来学习 数据删除，了解如何删除数据！
最后更新：2026-03-29