SQL Server数据更新 #

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

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    email VARCHAR(100),
    age INT,
    status BIT DEFAULT 1,
    salary DECIMAL(10,2),
    updated_at DATETIME2
);

INSERT INTO users VALUES
    (1, 'John', 'john@example.com', 25, 1, 5000.00, NULL),
    (2, 'Jane', 'jane@example.com', 30, 1, 6000.00, NULL),
    (3, 'Bob', 'bob@example.com', 28, 0, 4500.00, NULL),
    (4, 'Alice', 'alice@example.com', 22, 1, 4000.00, NULL),
    (5, 'Charlie', 'charlie@example.com', 35, 0, 7000.00, NULL);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    budget DECIMAL(12,2)
);

INSERT INTO departments VALUES
    (1, 'IT', 100000.00),
    (2, 'HR', 50000.00);

-- 更新单列
UPDATE users SET age = 26 WHERE id = 1;

-- 更新多行
UPDATE users SET status = 1 WHERE status = 0;

-- 更新所有行（危险操作！）
UPDATE users SET updated_at = GETDATE();

-- 更新多列
UPDATE users 
SET 
    age = 26,
    email = 'john_new@example.com',
    updated_at = GETDATE()
WHERE id = 1;

-- 使用表达式更新
UPDATE users 
SET 
    salary = salary * 1.1,  -- 加薪10%
    updated_at = GETDATE()
WHERE status = 1;

-- 重置为默认值
UPDATE users SET status = DEFAULT WHERE id = 1;

-- 重置为NULL
UPDATE users SET updated_at = NULL WHERE id = 1;

-- 等于条件
UPDATE users SET status = 0 WHERE id = 1;

-- 范围条件
UPDATE users SET salary = salary * 1.05 
WHERE age BETWEEN 25 AND 30;

-- IN条件
UPDATE users SET status = 1 
WHERE id IN (1, 2, 3);

-- LIKE条件
UPDATE users SET status = 0 
WHERE email LIKE '%@test.com';

-- NULL条件
UPDATE users SET updated_at = GETDATE() 
WHERE updated_at IS NULL;

-- 多条件组合
UPDATE users 
SET salary = salary * 1.1
WHERE status = 1 
  AND age > 25 
  AND salary < 5000;

-- 子查询条件
UPDATE users 
SET status = 0
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE status = 'cancelled'
);

-- EXISTS条件
UPDATE users u
SET status = 0
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.amount > 10000
);

-- 更新前N行
UPDATE TOP (10) users 
SET status = 1
WHERE status = 0;

-- 更新前10%行
UPDATE TOP (10) PERCENT users
SET salary = salary * 1.05;

-- 更新工资最低的5人
WITH cte AS (
    SELECT TOP 5 id
    FROM users
    ORDER BY salary ASC
)
UPDATE users
SET salary = salary * 1.1
WHERE id IN (SELECT id FROM cte);

-- 或者使用CTE直接更新
WITH cte AS (
    SELECT TOP 5 *
    FROM users
    ORDER BY salary ASC
)
UPDATE cte
SET salary = salary * 1.1;

-- 使用FROM子句更新
UPDATE u
SET u.salary = u.salary * 1.1
FROM users u
INNER JOIN departments d ON u.department_id = d.id
WHERE d.name = 'IT';

-- 更新多表关联数据
UPDATE o
SET o.status = 'completed'
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

-- 使用子查询值更新
UPDATE users
SET salary = (
    SELECT AVG(salary) FROM users
)
WHERE id = 1;

-- 使用相关子查询
UPDATE users u
SET salary = (
    SELECT AVG(salary) 
    FROM users 
    WHERE department_id = u.department_id
);

-- 使用CTE更新
WITH UserStats AS (
    SELECT 
        u.id,
        COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id
)
UPDATE u
SET u.order_count = us.order_count
FROM users u
INNER JOIN UserStats us ON u.id = us.id;

-- 返回更新前后数据
UPDATE users
SET salary = salary * 1.1
OUTPUT 
    inserted.id,
    deleted.salary AS old_salary,
    inserted.salary AS new_salary
WHERE id = 1;

-- 结果
-- id | old_salary | new_salary
-- 1  | 5000.00    | 5500.00

-- 创建审计表
CREATE TABLE salary_audit (
    user_id INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_at DATETIME DEFAULT GETDATE()
);

-- 更新并记录
UPDATE users
SET salary = salary * 1.1
OUTPUT 
    inserted.id,
    deleted.salary,
    inserted.salary
INTO salary_audit (user_id, old_salary, new_salary)
WHERE status = 1;

-- 使用CASE条件更新
UPDATE users
SET status = CASE 
    WHEN age < 25 THEN 0
    WHEN age >= 25 AND age < 35 THEN 1
    ELSE 2
END;

-- 根据不同条件更新不同值
UPDATE products
SET price = CASE 
    WHEN category = 'Electronics' THEN price * 0.9
    WHEN category = 'Clothing' THEN price * 0.8
    ELSE price * 0.95
END;

-- 同时更新多列
UPDATE users
SET 
    status = CASE WHEN salary > 6000 THEN 1 ELSE 0 END,
    salary = CASE WHEN salary < 5000 THEN salary * 1.2 ELSE salary END
WHERE id IN (1, 2, 3);

-- 使用变量更新
DECLARE @newStatus BIT = 1;
DECLARE @raiseRate DECIMAL(3,2) = 0.1;

UPDATE users
SET 
    status = @newStatus,
    salary = salary * (1 + @raiseRate)
WHERE id = 1;

-- 更新并获取值到变量
DECLARE @oldSalary DECIMAL(10,2);
DECLARE @newSalary DECIMAL(10,2);

UPDATE users
SET 
    salary = salary * 1.1,
    @oldSalary = deleted.salary,
    @newSalary = inserted.salary
OUTPUT deleted.salary, inserted.salary
WHERE id = 1;

PRINT '旧工资: ' + CAST(@oldSalary AS VARCHAR);
PRINT '新工资: ' + CAST(@newSalary AS VARCHAR);

-- 使用事务保证原子性
BEGIN TRANSACTION;

BEGIN TRY
    UPDATE users SET salary = salary * 1.1 WHERE id = 1;
    UPDATE users SET salary = salary * 0.9 WHERE id = 2;
    
    COMMIT TRANSACTION;
    PRINT '更新成功';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT '更新失败: ' + ERROR_MESSAGE();
END CATCH

-- 使用版本号实现乐观锁
DECLARE @version INT;
SELECT @version = version FROM users WHERE id = 1;

UPDATE users
SET 
    salary = salary * 1.1,
    version = version + 1
WHERE id = 1 AND version = @version;

IF @@ROWCOUNT = 0
    PRINT '更新失败，数据已被修改';
ELSE
    PRINT '更新成功';

-- 创建临时表存储更新数据
CREATE TABLE #updates (
    user_id INT,
    new_salary DECIMAL(10,2)
);

INSERT INTO #updates VALUES
    (1, 5500.00),
    (2, 6500.00),
    (3, 4800.00);

-- 批量更新
UPDATE u
SET u.salary = up.new_salary
FROM users u
INNER JOIN #updates up ON u.id = up.user_id;

DROP TABLE #updates;

-- 使用MERGE进行更新
MERGE INTO users AS target
USING (
    SELECT id, salary FROM users_staging
) AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET 
        target.salary = source.salary,
        target.updated_at = GETDATE();

-- 确保WHERE条件使用索引
-- 创建索引
CREATE INDEX ix_users_status ON users(status);

-- 使用索引列更新
UPDATE users 
SET salary = salary * 1.1
WHERE status = 1;  -- 使用索引

-- 大表分批更新
DECLARE @batchSize INT = 1000;
DECLARE @rowsAffected INT = 1;

WHILE @rowsAffected > 0
BEGIN
    UPDATE TOP (@batchSize) users
    SET salary = salary * 1.1
    WHERE status = 1;
    
    SET @rowsAffected = @@ROWCOUNT;
    
    -- 延迟减少锁争用
    WAITFOR DELAY '00:00:00.1';
END

-- 大批量更新时禁用触发器
DISABLE TRIGGER ALL ON users;

-- 执行更新
UPDATE users SET salary = salary * 1.1;

-- 启用触发器
ENABLE TRIGGER ALL ON users;

-- 检查WHERE条件
-- 安全做法：先查询再更新
BEGIN TRANSACTION;

-- 先查看影响行数
SELECT COUNT(*) FROM users WHERE status = 1;

-- 确认后再更新
UPDATE users SET salary = salary * 1.1 WHERE status = 1;

-- 检查结果
SELECT * FROM users WHERE status = 1;

-- 确认无误后提交
COMMIT TRANSACTION;

-- 设置防止全表更新错误
-- SSMS: Tools → Options → Query Execution → SQL Server → ANSI
-- 勾选 "SET ANSI_NULL_DFLT_ON"

-- 使用@@ROWCOUNT
UPDATE users SET salary = salary * 1.1 WHERE status = 1;

IF @@ROWCOUNT = 0
    PRINT '没有数据被更新';
ELSE
    PRINT '更新了 ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 行';

-- 更新并验证
DECLARE @expectedSalary DECIMAL(10,2) = 5500.00;

UPDATE users SET salary = @expectedSalary WHERE id = 1;

IF EXISTS (
    SELECT 1 FROM users 
    WHERE id = 1 AND salary = @expectedSalary
)
    PRINT '更新验证成功';
ELSE
    PRINT '更新验证失败';

-- 1. 违反约束
UPDATE users SET age = -1 WHERE id = 1;  -- CHECK约束

-- 2. 外键约束
UPDATE orders SET user_id = 999 WHERE id = 1;  -- 外键不存在

-- 3. 唯一约束
UPDATE users SET email = 'john@example.com' WHERE id = 2;  -- 邮箱重复

-- 4. 锁超时
-- 其他事务持有锁

-- 使用WITH (NOLOCK)读取（仅查询）
SELECT * FROM users WITH (NOLOCK);

-- 使用UPDLOCK锁定要更新的行
BEGIN TRANSACTION;

SELECT * FROM users WITH (UPDLOCK) WHERE id = 1;

UPDATE users SET salary = salary * 1.1 WHERE id = 1;

COMMIT TRANSACTION;