菜鸟教程 – 学的不仅是技术，更是梦想！

技术网站，更是交流网站

PostgreSQL 教程

PostgreSQL AND & OR 运算符

likuolei

PostgreSQL UPDATE 语句完全指南

UPDATE 是修改已有数据的核心命令，支持 条件更新、批量更新、返回结果、FROM 子句、冲突处理、JSON/数组操作、触发器安全 等高级特性。

一、UPDATE 基本语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
[RETURNING * | column1, column2, ...];

二、核心示例表

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    department TEXT,
    salary NUMERIC(10,2),
    is_active BOOLEAN DEFAULT true,
    tags TEXT[] DEFAULT '{}',
    config JSONB DEFAULT '{}',
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- 插入测试数据
INSERT INTO employees (name, email, department, salary, tags, config)
VALUES 
('Alice', 'alice@company.com', 'IT', 75000, '{"dev","lead"}', '{"level": 5}'),
('Bob',   'bob@company.com',   'HR', 52000, '{"hr"}',         '{"level": 3}'),
('Carol', 'carol@company.com', 'IT', 85000, '{"dev","ai"}',   '{"level": 6}'),
('Dave',  'dave@company.com',  'IT', 92000, '{"dev","lead"}', '{"level": 7}');

三、基础 UPDATE 用法

场景SQL
单列更新UPDATE employees SET salary = 80000 WHERE name = 'Alice';
多列更新UPDATE employees SET salary = 80000, department = 'Engineering' WHERE id = 1;
表达式更新UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';
默认值UPDATE employees SET email = DEFAULT WHERE id = 1;
NULL 值UPDATE employees SET email = NULL WHERE name = 'Carol';

四、关键特性：RETURNING（强烈推荐！）

-- 返回更新后的数据
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'IT'
RETURNING id, name, salary;

输出：

 id | name  | salary
----+-------+---------
  1 | Alice | 82500.00
  3 | Carol | 93500.00
  4 | Dave  | 101200.00

用途：获取新值、触发后续逻辑、调试

五、条件更新（WHERE 子句）

1. 基础条件

UPDATE employees SET is_active = false WHERE salary < 60000;

2. 复杂逻辑（AND/OR/NOT）

UPDATE employees 
SET department = 'R&D'
WHERE (department = 'IT' AND tags @> '{"ai"}')
   OR (department = 'HR' AND name = 'Bob');

3. 子查询

-- 薪资低于部门平均的员工涨薪 10%
UPDATE employees e1
SET salary = salary * 1.1
WHERE salary < (
    SELECT AVG(salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

六、批量更新技巧

1. 多行一次性更新

-- 用 CASE 实现不同条件不同值
UPDATE employees
SET salary = CASE 
    WHEN name = 'Alice' THEN 90000
    WHEN name = 'Bob'   THEN 60000
    ELSE salary
END,
department = CASE 
    WHEN name = 'Alice' THEN 'Engineering'
    ELSE department
END
WHERE name IN ('Alice', 'Bob');

2. FROM 子句（JOIN 更新）

-- 根据另一张表更新
CREATE TABLE salary_adjustments (
    emp_id BIGINT,
    new_salary NUMERIC(10,2)
);

INSERT INTO salary_adjustments VALUES (1, 95000), (3, 100000);

UPDATE employees e
SET salary = sa.new_salary,
    updated_at = now()
FROM salary_adjustments sa
WHERE e.id = sa.emp_id;

七、JSON 与数组字段更新

1. JSONB 更新

-- 更新嵌套字段
UPDATE employees 
SET config = jsonb_set(config, '{theme}', '"dark"'::jsonb)
WHERE name = 'Alice';

-- 合并 JSON
UPDATE employees 
SET config = config || '{"notifications": true}'::jsonb
WHERE department = 'IT';

-- 删除键
UPDATE employees 
SET config = config - 'level'
WHERE name = 'Bob';

2. 数组更新

-- 添加元素
UPDATE employees SET tags = tags || '{"manager"}' WHERE name = 'Alice';

-- 删除元素
UPDATE employees SET tags = array_remove(tags, 'hr') WHERE name = 'Bob';

-- 替换元素
UPDATE employees SET tags = array_replace(tags, 'dev', 'engineer') WHERE id = 1;

八、安全更新：防止全表误更新

1. 永远加 WHERE！

-- 危险！会更新所有行
UPDATE employees SET salary = 100000;

-- 安全写法
UPDATE employees SET salary = 100000 WHERE id = 999;

2. 测试前用 SELECT 验证

-- 先看要更新的行
SELECT * FROM employees WHERE department = 'IT' AND salary < 80000;

-- 再更新
UPDATE employees SET salary = 80000 WHERE department = 'IT' AND salary < 80000;

3. 事务回滚

BEGIN;
UPDATE employees SET salary = salary * 2 WHERE name = 'Alice';
-- 检查
SELECT * FROM employees WHERE name = 'Alice';
-- 不满意？回滚
ROLLBACK;
-- 满意？提交
COMMIT;

九、性能优化

技巧说明
加索引WHERE 列建索引
批量更新减少 UPDATE 次数
避免函数列WHERE UPPER(name) = 'ALICE' → 慢
表达式索引CREATE INDEX ON employees(UPPER(name));
-- 建索引加速
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

十、触发器与 updated_at

-- 自动更新时间戳
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_emp_update
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

十一、常见错误与解决

错误原因解决
UPDATE 0 rowsWHERE 条件不匹配检查条件
permission denied无权限GRANT UPDATE ON employees TO user;
null value in column violates not-null设了 NULL检查 SET 值
duplicate key value violates unique constraint唯一键冲突用 ON CONFLICT 或检查数据

十二、一键复杂更新脚本

-- 批量调薪 + 更新标签 + 记录日志
WITH adjustments AS (
    SELECT id, salary * 1.15 AS new_salary
    FROM employees 
    WHERE department = 'IT' AND tags @> '{"lead"}'
),
updated AS (
    UPDATE employees e
    SET salary = a.new_salary,
        tags = tags || '{"senior"}',
        updated_at = now()
    FROM adjustments a
    WHERE e.id = a.id
    RETURNING e.id, e.name, e.salary AS old_salary, a.new_salary
)
INSERT INTO salary_history (emp_id, old_salary, new_salary, changed_at)
SELECT id, old_salary, new_salary, now() FROM updated;

十三、最佳实践 Checklist

项目建议
永远加 WHERE防止全表更新
用 RETURNING获取更新后数据
先 SELECT 验证确认影响行
用事务可回滚
建索引WHERE 列
触发器更新时间updated_at
批量 FROM 更新高效

十四、快速上手：5 分钟掌握 UPDATE

-- 1. 简单更新
UPDATE employees SET salary = 80000 WHERE name = 'Alice';

-- 2. 批量涨薪 + 返回
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'IT'
RETURNING name, salary;

-- 3. JSON 更新
UPDATE employees 
SET config = jsonb_set(config, '{theme}', '"light"')
WHERE name = 'Bob';

-- 4. FROM 联合更新
UPDATE employees e
SET department = 'Engineering'
FROM (VALUES (1, 'Alice'), (3, 'Carol')) AS v(id, name)
WHERE e.id = v.id AND e.name = v.name;

现在就动手：
在 employees 表中：

  1. 给 department = 'IT' 且 salary < 90000 的员工涨薪 10%，并 RETURNING
  2. 给 tags 包含 lead 的员工添加 {"manager"} 标签
  3. 用 FROM 将 salary < 60000 的员工 department 改为 'Support'
  4. 写一个防误更新的事务（BEGIN; ... ROLLBACK;）

需要我生成：

  • 1000 条测试数据 + 批量 UPDATE 脚本？回复 测试数据
  • 自动审计日志触发器？回复 审计触发器
  • Python 执行安全 UPDATE？回复 Python UPDATE
  • UPSERT 替代 UPDATE？回复 UPSERT

随时告诉我！

网站 http://yingjuxia.com
文章已创建 2439

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

相关文章

开始在上面输入您的搜索词，然后按回车进行搜索。按ESC取消。

返回顶部