更新数据UPDATE - 元素码农

发布时间: 2025-04-02 22:53

↑

# 更新数据UPDATE

本文将详细介绍如何在SQLite数据库中更新数据，包括基本更新操作、条件更新以及更新时的注意事项和最佳实践。

## 基本更新语法

### 单列更新

1. **基本语法**
   ```sql
   UPDATE table_name
   SET column_name = value
   WHERE condition;
   ```

2. **示例**
   ```sql
   UPDATE users
   SET status = 'active'
   WHERE id = 1;
   ```

### 多列更新

1. **同时更新多列**
   ```sql
   UPDATE products
   SET 
       price = 19.99,
       stock = stock - 1,
       last_updated = CURRENT_TIMESTAMP
   WHERE id = 100;
   ```

2. **使用表达式**
   ```sql
   UPDATE orders
   SET 
       total_amount = quantity * unit_price,
       status = CASE 
           WHEN paid_amount >= total_amount THEN 'paid'
           ELSE 'pending'
       END
   WHERE order_date = CURRENT_DATE;
   ```

## 条件更新

### WHERE子句

1. **比较条件**
   ```sql
   -- 更新特定价格范围的产品
   UPDATE products
   SET category = 'premium'
   WHERE price >= 100;
   ```

2. **多条件组合**
   ```sql
   UPDATE users
   SET status = 'inactive'
   WHERE last_login < date('now', '-30 days')
   AND account_type = 'free';
   ```

### 子查询更新

1. **使用子查询条件**
   ```sql
   UPDATE products
   SET stock = stock - 1
   WHERE id IN (
       SELECT product_id
       FROM order_items
       WHERE order_id = 1001
   );
   ```

2. **使用子查询值**
   ```sql
   UPDATE orders
   SET total_amount = (
       SELECT SUM(quantity * unit_price)
       FROM order_items
       WHERE order_items.order_id = orders.id
   );
   ```

## 批量更新

### 事务处理

1. **使用事务**
   ```sql
   BEGIN TRANSACTION;
   
   UPDATE products
   SET price = price * 1.1
   WHERE category = 'electronics';
   
   UPDATE price_history
   SET update_date = CURRENT_TIMESTAMP
   WHERE product_id IN (
       SELECT id FROM products
       WHERE category = 'electronics'
   );
   
   COMMIT;
   ```

2. **错误处理**
   ```sql
   BEGIN TRANSACTION;
   
   UPDATE accounts
   SET balance = balance - 100
   WHERE id = 1;
   
   UPDATE accounts
   SET balance = balance + 100
   WHERE id = 2;
   
   -- 如果任何更新失败，回滚所有更改
   ROLLBACK;
   ```

### 批量条件更新

1. **CASE表达式**
   ```sql
   UPDATE products
   SET price = 
       CASE category
           WHEN 'electronics' THEN price * 1.1
           WHEN 'clothing' THEN price * 0.9
           ELSE price
       END;
   ```

2. **多表关联更新**
   ```sql
   UPDATE orders
   SET status = 'shipped'
   WHERE id IN (
       SELECT order_id
       FROM shipments
       WHERE shipping_date = CURRENT_DATE
   );
   ```

## 特殊更新操作

### UPSERT操作

1. **INSERT OR REPLACE**
   ```sql
   INSERT OR REPLACE INTO users (id, username, email)
   VALUES (1, 'john_doe', 'new_email@example.com');
   ```

2. **ON CONFLICT**
   ```sql
   INSERT INTO users (username, email)
   VALUES ('john_doe', 'john@example.com')
   ON CONFLICT(username) DO UPDATE SET
       email = excluded.email,
       updated_at = CURRENT_TIMESTAMP;
   ```

### 条件表达式

1. **COALESCE使用**
   ```sql
   UPDATE users
   SET last_login = COALESCE(last_login, created_at)
   WHERE last_login IS NULL;
   ```

2. **NULLIF使用**
   ```sql
   UPDATE products
   SET discount_price = NULLIF(regular_price, sale_price)
   WHERE sale_price IS NOT NULL;
   ```

## 性能优化

### 索引使用

1. **利用索引**
   ```sql
   -- 使用索引列作为更新条件
   UPDATE users
   SET status = 'active'
   WHERE email = 'user@example.com';
   ```

2. **避免索引更新**
   ```sql
   -- 批量更新前禁用索引
   PRAGMA defer_foreign_keys = ON;
   
   -- 执行更新
   UPDATE large_table SET column1 = value1;
   
   -- 更新后重建索引
   REINDEX table_name;
   ```

### 优化策略

1. **分批更新**
   ```sql
   -- 每次更新一部分数据
   UPDATE users
   SET status = 'inactive'
   WHERE id IN (
       SELECT id FROM users
       WHERE last_login < date('now', '-30 days')
       LIMIT 1000
   );
   ```

2. **使用临时表**
   ```sql
   -- 创建临时表存储要更新的ID
   CREATE TEMP TABLE tmp_updates (
       id INTEGER PRIMARY KEY
   );
   
   -- 插入要更新的ID
   INSERT INTO tmp_updates
   SELECT id FROM users
   WHERE last_login < date('now', '-30 days');
   
   -- 使用临时表更新
   UPDATE users
   SET status = 'inactive'
   WHERE id IN (SELECT id FROM tmp_updates);
   ```

## 最佳实践

### 安全更新

1. **使用事务**
   - 保证数据一致性
   - 支持回滚操作
   - 处理并发问题

2. **条件验证**
   - 更新前验证数据
   - 检查受影响行数
   - 保留更新日志

### 性能考虑

1. **批量操作**
   - 合适的批次大小
   - 使用事务包装
   - 监控性能影响

2. **资源管理**
   - 控制锁定时间
   - 避免长事务
   - 合理使用索引

## 总结

更新数据需要注意：

- 正确使用WHERE子句
- 保证数据一致性
- 注意性能优化
- 做好错误处理

建议：

- 使用事务保护
- 验证更新结果
- 优化更新策略
- 注意并发处理