SQLite数据更新 #

-- UPDATE 基本语法
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

-- 示例表结构
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    status INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME
);

INSERT INTO users (name, email, age, status) VALUES
    ('John', 'john@example.com', 25, 1),
    ('Jane', 'jane@example.com', 30, 1),
    ('Bob', 'bob@example.com', 28, 0);

-- SQLite UPDATE 特点：
-- 1. 可以更新单个或多个列
-- 2. 支持表达式和函数
-- 3. 支持子查询
-- 4. 支持 FROM 子句（3.33+）
-- 5. 支持 RETURNING 子句（3.35+）

-- 更新单个列
UPDATE users
SET status = 0
WHERE id = 1;

-- 查看结果
SELECT id, name, status FROM users WHERE id = 1;
-- id | name | status
-- ---|------|--------
-- 1  | John | 0

-- 更新多个列
UPDATE users
SET 
    age = 26,
    status = 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- 查看结果
SELECT id, name, age, status, updated_at FROM users WHERE id = 1;

-- 不带WHERE条件，更新所有行
UPDATE users
SET status = 1;

-- 危险操作！确保这是你想要的
-- 建议先查询确认
SELECT COUNT(*) FROM users WHERE status != 1;

-- 使用表达式更新
UPDATE users
SET age = age + 1
WHERE id = 1;

-- 使用函数
UPDATE users
SET 
    email = lower(email),
    name = upper(name)
WHERE id = 1;

-- 使用计算表达式
UPDATE products
SET 
    price = price * 1.1,
    discount = CASE 
        WHEN price > 100 THEN 0.1
        ELSE 0.05
    END;

-- 使用比较运算符
UPDATE users SET status = 0 WHERE age > 30;
UPDATE users SET status = 1 WHERE age <= 30;
UPDATE users SET status = 1 WHERE age BETWEEN 20 AND 30;

-- 使用逻辑运算符
UPDATE users 
SET status = 0 
WHERE age > 30 AND status = 1;

-- 使用IN
UPDATE users 
SET status = 0 
WHERE id IN (1, 3, 5);

-- 使用子查询
UPDATE users 
SET status = 0 
WHERE id IN (SELECT user_id FROM inactive_users);

-- 使用LIKE
UPDATE users 
SET email = lower(email) 
WHERE email LIKE '%@Example.COM';

-- 使用CASE表达式条件更新
UPDATE users
SET status = CASE
    WHEN age < 20 THEN 0
    WHEN age BETWEEN 20 AND 30 THEN 1
    WHEN age > 30 THEN 2
    ELSE 0
END;

-- 复杂条件更新
UPDATE products
SET price = CASE
    WHEN category = 'Electronics' THEN price * 0.9
    WHEN category = 'Books' THEN price * 0.8
    WHEN category = 'Clothing' THEN price * 0.7
    ELSE price
END
WHERE stock > 100;

-- 使用子查询获取更新值
UPDATE users
SET status = (
    SELECT COUNT(*) 
    FROM orders 
    WHERE orders.user_id = users.id
);

-- 使用子查询作为条件
UPDATE users
SET status = 0
WHERE id NOT IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE order_date > date('now', '-30 days')
);

-- 使用EXISTS
UPDATE users
SET status = 1
WHERE EXISTS (
    SELECT 1 
    FROM orders 
    WHERE orders.user_id = users.id 
    AND orders.total > 1000
);

-- 使用FROM子句从其他表获取数据
UPDATE orders
SET 
    customer_name = customers.name,
    customer_email = customers.email
FROM customers
WHERE orders.customer_id = customers.id;

-- 更新多个表的数据
UPDATE products
SET 
    category_name = categories.name,
    category_desc = categories.description
FROM categories
WHERE products.category_id = categories.id;

-- 使用JOIN更新
UPDATE orders
SET total_amount = order_totals.total
FROM (
    SELECT order_id, SUM(quantity * price) AS total
    FROM order_items
    GROUP BY order_id
) AS order_totals
WHERE orders.id = order_totals.order_id;

-- 使用多个表
UPDATE users
SET last_order_date = orders.order_date
FROM orders
WHERE users.id = orders.user_id
AND orders.id = (
    SELECT MAX(id) FROM orders o2 WHERE o2.user_id = users.id
);

-- 返回更新后的数据
UPDATE users
SET status = 0, updated_at = CURRENT_TIMESTAMP
WHERE id = 1
RETURNING id, name, status, updated_at;

-- 返回所有列
UPDATE users
SET age = age + 1
WHERE id = 1
RETURNING *;

-- 返回计算值
UPDATE products
SET price = price * 1.1
WHERE id = 1
RETURNING 
    id, 
    name, 
    price AS new_price,
    price / 1.1 AS old_price;

-- 批量更新并返回
UPDATE users
SET status = 1
WHERE age BETWEEN 20 AND 30
RETURNING id, name, status;

-- 返回更新的行数
SELECT changes();

-- SQLite 3.24.0+ 支持 UPSERT
INSERT INTO users (id, name, email, age)
VALUES (1, 'John Updated', 'john@example.com', 26)
ON CONFLICT(id) DO UPDATE SET
    name = excluded.name,
    age = excluded.age,
    updated_at = CURRENT_TIMESTAMP;

-- 使用唯一约束
INSERT INTO users (email, name, age)
VALUES ('john@example.com', 'John', 26)
ON CONFLICT(email) DO UPDATE SET
    name = excluded.name,
    age = excluded.age;

-- 条件更新
INSERT INTO users (id, name, email, age)
VALUES (1, 'John', 'john@example.com', 26)
ON CONFLICT(id) DO UPDATE SET
    age = excluded.age
WHERE excluded.age > users.age;

-- UPDATE：只能更新已存在的行
UPDATE users SET name = 'John' WHERE id = 1;

-- UPSERT：不存在则插入，存在则更新
INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

-- 选择建议：
-- 确定行存在：使用 UPDATE
-- 不确定行是否存在：使用 UPSERT

-- 将值设置为NULL
UPDATE users
SET age = NULL
WHERE id = 1;

-- 条件设置为NULL
UPDATE users
SET phone = NULL
WHERE phone = '';

-- 使用COALESCE处理NULL
UPDATE users
SET age = COALESCE(age, 0) + 1;

-- 使用IFNULL
UPDATE users
SET status = IFNULL(status, 0);

-- 使用NULLIF
UPDATE users
SET phone = NULLIF(phone, '');

-- 更新为当前时间
UPDATE users
SET updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- 使用datetime函数
UPDATE users
SET updated_at = datetime('now')
WHERE id = 1;

-- 日期计算
UPDATE events
SET event_date = date(event_date, '+7 days')
WHERE id = 1;

UPDATE events
SET event_datetime = datetime(event_datetime, '+1 hour')
WHERE id = 1;

-- 更新过期记录
UPDATE subscriptions
SET status = 'expired'
WHERE end_date < date('now');

-- 更新最近活跃时间
UPDATE users
SET last_login = datetime('now')
WHERE id = 1;

-- 更新整个JSON
UPDATE products
SET attributes = '{"brand": "Dell", "ram": "32GB"}'
WHERE id = 1;

-- 使用JSON函数更新
UPDATE products
SET attributes = json_set(attributes, '$.ram', '32GB')
WHERE id = 1;

-- 更新嵌套JSON
UPDATE products
SET attributes = json_set(attributes, '$.specs.cpu', 'Intel i7')
WHERE id = 1;

-- 删除JSON属性
UPDATE products
SET attributes = json_remove(attributes, '$.old_field')
WHERE id = 1;

-- 错误：忘记WHERE条件
UPDATE users SET status = 0;  -- 更新所有行！

-- 安全做法：先查询
SELECT COUNT(*) FROM users WHERE status != 0;
-- 确认后再执行

-- 使用事务
BEGIN TRANSACTION;
UPDATE users SET status = 0 WHERE id = 1;
-- 检查结果
SELECT * FROM users WHERE id = 1;
-- 确认无误后提交
COMMIT;
-- 或回滚
ROLLBACK;

-- SQLite 不支持 UPDATE LIMIT
-- 变通方法：使用子查询

UPDATE users
SET status = 0
WHERE id IN (
    SELECT id FROM users 
    WHERE status = 1 
    LIMIT 10
);

-- 使用参数化查询防止SQL注入
-- Python
-- cursor.execute(
--     "UPDATE users SET name = ?, status = ? WHERE id = ?",
--     (name, status, id)
-- )

-- PHP
-- $stmt = $pdo->prepare("UPDATE users SET name = ? WHERE id = ?");
-- $stmt->execute([$name, $id]);

-- 使用事务包装批量更新
BEGIN TRANSACTION;
UPDATE users SET status = 0 WHERE id = 1;
UPDATE users SET status = 0 WHERE id = 2;
UPDATE users SET status = 0 WHERE id = 3;
-- ...
COMMIT;

-- 使用CASE表达式减少UPDATE语句
UPDATE users
SET status = CASE id
    WHEN 1 THEN 0
    WHEN 2 THEN 0
    WHEN 3 THEN 0
    ELSE status
END
WHERE id IN (1, 2, 3);

-- 确保WHERE条件的列有索引
CREATE INDEX idx_users_status ON users(status);

-- 更新时使用索引
UPDATE users SET name = 'New Name' WHERE status = 1;

-- 更新返回0行
UPDATE users SET name = 'Test' WHERE id = 999;

-- 检查影响的行数
SELECT changes();  -- 返回0

-- 解决方案：确认条件是否正确
SELECT * FROM users WHERE id = 999;

-- 启用外键
PRAGMA foreign_keys = ON;

-- 错误：外键约束失败
UPDATE orders SET user_id = 999 WHERE id = 1;

-- 解决方案：确保外键值存在
SELECT * FROM users WHERE id = 999;

-- 错误：UNIQUE constraint failed
UPDATE users SET email = 'jane@example.com' WHERE id = 1;

-- 解决方案：确保新值唯一
SELECT * FROM users WHERE email = 'jane@example.com';