SQLUPDATE语句详解更新单列、多列的写法

SQL UPDATE语句详解

一、UPDATE语句基本语法

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

二、更新单列

基本示例

-- 更新所有记录
UPDATE employees SET salary = 50000;

-- 按条件更新
UPDATE employees 
SET salary = 55000 
WHERE department = 'IT';

-- 使用表达式更新
UPDATE products 
SET price = price * 1.1  -- 价格上浮10%
WHERE category = 'Electronics';

实际应用示例

-- 1. 更新用户最后登录时间
UPDATE users 
SET last_login = CURRENT_TIMESTAMP 
WHERE user_id = 123;

-- 2. 增加库存
UPDATE inventory 
SET quantity = quantity + 10 
WHERE product_id = 456;

-- 3. 状态更新
UPDATE orders 
SET status = 'Shipped' 
WHERE order_id = 789 AND status = 'Processing';

三、更新多列

同时更新多列

-- 更新多个字段
UPDATE employees 
SET 
    salary = 60000,
    department = 'Engineering',
    last_promotion_date = '2024-01-15'
WHERE employee_id = 101;

-- 更新客户信息
UPDATE customers 
SET 
    email = 'newemail@example.com',
    phone = '123-456-7890',
    address = '123 Main St'
WHERE customer_id = 202;

基于计算更新多列

-- 根据公式计算更新
UPDATE sales 
SET 
    total_price = quantity * unit_price,
    discount_amount = total_price * 0.1,  -- 10%折扣
    final_price = total_price - discount_amount
WHERE sale_id = 303;

-- 更新员工信息
UPDATE employees 
SET 
    salary = salary * 1.05,  -- 加薪5%
    bonus = salary * 0.15,   -- 奖金为薪水的15%
    updated_at = NOW()
WHERE performance_rating >= 4;

四、使用子查询更新

从其他表获取更新值

-- 根据另一表数据更新
UPDATE employees e
SET e.department = (
    SELECT d.department_name 
    FROM departments d 
    WHERE d.manager_id = e.employee_id
)
WHERE e.is_manager = 1;

-- 使用JOIN更新（MySQL写法）
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET 
    o.customer_name = c.full_name,
    o.customer_email = c.email
WHERE o.order_date > '2024-01-01';

-- 使用JOIN更新（SQL Server写法）
UPDATE o
SET 
    o.customer_name = c.full_name,
    o.customer_email = c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2024-01-01';

-- 使用JOIN更新（PostgreSQL写法）
UPDATE orders o
SET 
    customer_name = c.full_name,
    customer_email = c.email
FROM customers c
WHERE o.customer_id = c.customer_id 
AND o.order_date > '2024-01-01';

批量更新示例

-- 根据产品类别更新价格
UPDATE products p
SET 
    p.price = p.price * CASE 
        WHEN p.category = 'Electronics' THEN 1.1
        WHEN p.category = 'Clothing' THEN 1.05
        ELSE 1.02
    END,
    p.updated_at = CURRENT_TIMESTAMP
WHERE p.in_stock = 1;

五、条件更新（CASE语句）

-- 根据条件不同值更新
UPDATE students 
SET grade = 
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END,
    status = 
    CASE 
        WHEN score >= 60 THEN 'Passed'
        ELSE 'Failed'
    END
WHERE exam_id = 1;

-- 根据销售额设置员工级别
UPDATE sales_staff 
SET 
    commission_rate = 
        CASE 
            WHEN total_sales > 100000 THEN 0.15
            WHEN total_sales > 50000 THEN 0.10
            ELSE 0.05
        END,
    performance_level = 
        CASE 
            WHEN total_sales > 100000 THEN 'Gold'
            WHEN total_sales > 50000 THEN 'Silver'
            ELSE 'Bronze'
        END
WHERE year = 2024;

六、更新部分字段为NULL

-- 设置字段为NULL
UPDATE employees 
SET middle_name = NULL 
WHERE employee_id = 101;

-- 清空多个字段
UPDATE orders 
SET 
    shipped_date = NULL,
    tracking_number = NULL,
    shipper_id = NULL
WHERE status = 'Cancelled';

七、最佳实践和注意事项

1. 始终使用WHERE子句（除非确实需要更新所有行）

-- ❌ 危险：更新所有行
UPDATE users SET last_active = NOW();

-- ✅ 安全：指定条件
UPDATE users SET last_active = NOW() WHERE user_id = 123;

2. 事务处理

BEGIN TRANSACTION;

UPDATE accounts 
SET balance = balance - 100 
WHERE account_id = 1;

UPDATE accounts 
SET balance = balance + 100 
WHERE account_id = 2;

-- 检查是否成功
IF @@ERROR = 0
    COMMIT TRANSACTION;
ELSE
    ROLLBACK TRANSACTION;

3. LIMIT限制（MySQL）

-- 只更新前10条记录
UPDATE products 
SET price = price * 0.9 
WHERE category = 'Electronics'
LIMIT 10;

4. 更新前先查询

-- 先查询受影响的行
SELECT * FROM employees 
WHERE department = 'HR' AND salary < 50000;

-- 再执行更新
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'HR' AND salary < 50000;

八、常见错误示例

-- 错误：缺少WHERE条件（意外更新所有行）
UPDATE customers SET status = 'Inactive';

-- 错误：字段名拼写错误
UPDATE users SET last_login_datee = NOW();  -- last_login_datee不存在

-- 错误：类型不匹配
UPDATE products SET price = 'cheap' WHERE id = 1;  -- price应该是数字类型

九、性能优化建议

索引优化：确保WHERE条件中的字段有索引 批量更新：尽量减少单次更新语句的数量 避免全表扫描：使用合适的WHERE条件 分页更新：大量数据更新时分批次进行

-- 分页更新示例（MySQL）
UPDATE large_table 
SET status = 'processed'
WHERE status = 'pending'
LIMIT 1000;
-- 重复执行直到完成

掌握这些UPDATE语句的用法和注意事项，可以帮助你更安全、高效地管理数据库中的数据更新操作。