SQL Update更新数据的操作方法

SQL的UPDATE语句用于修改表中已有的数据。以下是各种操作方法的详细说明：

一、基本UPDATE语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例：

-- 更新单个字段
UPDATE employees
SET salary = 50000
WHERE id = 1;

-- 更新多个字段
UPDATE employees
SET salary = 55000, department = 'IT'
WHERE id = 2;

二、带条件的更新

1. 使用WHERE子句

-- 更新特定条件的记录
UPDATE products
SET price = price * 1.1  -- 涨价10%
WHERE category = 'Electronics';

-- 使用AND/OR条件
UPDATE orders
SET status = 'Completed'
WHERE order_date < '2024-01-01' AND status = 'Pending';

2. 使用IN操作符

UPDATE customers
SET vip_level = 'Gold'
WHERE customer_id IN (101, 102, 103);

三、使用子查询更新

1. 基于子查询的结果更新

-- 根据其他表更新数据
UPDATE employees e
SET e.salary = e.salary * 1.05
WHERE e.department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location = 'New York'
);

-- 使用连接更新（MySQL）
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.discount = 0.1
WHERE c.vip_level = 'Platinum';

2. 使用关联子查询

-- 更新为最大值
UPDATE products p
SET p.price = (
    SELECT MAX(price) 
    FROM products 
    WHERE category = p.category
)
WHERE p.category = 'Electronics';

四、批量更新操作

-- 使用CASE语句条件更新
UPDATE employees
SET salary = CASE
    WHEN experience_years > 10 THEN salary * 1.15
    WHEN experience_years > 5 THEN salary * 1.10
    ELSE salary * 1.05
END;

-- 批量设置值
UPDATE users
SET status = 'active',
    last_login = CURRENT_TIMESTAMP
WHERE registration_date > '2024-01-01';

五、特殊更新方式

1. 更新为NULL

UPDATE products
SET discount = NULL
WHERE discontinued = 1;

2. 使用数学表达式

UPDATE inventory
SET quantity = quantity - sold_quantity,
    last_updated = NOW()
WHERE product_id = 100;

3. 使用系统函数

UPDATE logs
SET process_date = GETDATE()  -- SQL Server
WHERE status = 'processed';

UPDATE logs
SET process_date = NOW()  -- MySQL
WHERE status = 'processed';

六、注意事项和安全建议

1. 重要：先查询再更新

-- 先使用SELECT验证
SELECT * FROM employees 
WHERE department = 'Sales';

-- 确认后再执行UPDATE
UPDATE employees 
SET salary = salary * 1.1
WHERE department = 'Sales';

2. 使用事务确保数据一致性

BEGIN TRANSACTION;

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- 如果出错可以 ROLLBACK;

3. 限制更新行数

-- MySQL
UPDATE products
SET stock = 0
WHERE discontinued = 1
LIMIT 100;

-- SQL Server
UPDATE TOP(100) products
SET stock = 0
WHERE discontinued = 1;

七、不同数据库的语法差异

MySQL

-- 多表更新
UPDATE table1 t1, table2 t2
SET t1.column = t2.column
WHERE t1.id = t2.id;

PostgreSQL

-- 使用FROM子句
UPDATE employees e
SET salary = e.salary * 1.1
FROM departments d
WHERE e.department_id = d.id
AND d.location = 'Headquarters';

SQL Server

-- 使用OUTPUT子句
UPDATE employees
SET salary = salary * 1.1
OUTPUT 
    INSERTED.id,
    DELETED.salary AS old_salary,
    INSERTED.salary AS new_salary
WHERE department = 'IT';

八、最佳实践

总是备份数据：在执行大量更新前备份表 使用事务：特别是涉及多表更新时 添加索引：WHERE子句中使用的字段应有索引 分批更新：大量数据时分批更新，避免锁表 记录日志：重要的更新操作应记录日志

-- 分批更新示例
DECLARE @BatchSize INT = 1000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP(@BatchSize) products
    SET processed = 1
    WHERE processed = 0;

    SET @RowsAffected = @@ROWCOUNT;
END