TSQL：使用 With 语句更新值？

在 T-SQL 中，WITH 语句通常用于定义公用表表达式（CTE），这是一种临时结果集，可以在后续的 SELECT、INSERT、UPDATE 或 DELETE 语句中引用。你可以使用 CTE 来简化复杂的查询，并在更新操作中使用它们。

以下是一个使用 WITH 语句（CTE）来更新表中值的示例。

示例场景

假设你有一个名为 Employees 的表，包含以下列：

• EmployeeID (int)
• FirstName (nvarchar)
• LastName (nvarchar)
• Salary (decimal)

你想要更新所有工资低于 50000 的员工，将他们的工资增加 10%。

使用 CTE 更新值

1. 定义 CTE：首先定义一个 CTE，选择需要更新的记录。
2. 更新操作：在 UPDATE 语句中引用 CTE。

以下是具体的 SQL 代码：

-- 定义 CTE
WITH LowSalaryEmployees AS (
    SELECT 
        EmployeeID,
        Salary
    FROM 
        Employees
    WHERE 
        Salary < 50000
)
-- 使用 CTE 更新值
UPDATE Employees
SET Salary = Salary * 1.10
FROM Employees e
INNER JOIN LowSalaryEmployees lse
ON e.EmployeeID = lse.EmployeeID;

解释

1. 定义 CTE： WITH LowSalaryEmployees AS ( SELECT EmployeeID, Salary FROM Employees WHERE Salary < 50000 ) 这段代码定义了一个名为 LowSalaryEmployees 的 CTE，选择所有工资低于 50000 的员工。
2. 更新操作： UPDATE Employees SET Salary = Salary * 1.10 FROM Employees e INNER JOIN LowSalaryEmployees lse ON e.EmployeeID = lse.EmployeeID; 这段代码使用 CTE LowSalaryEmployees 来更新 Employees 表中符合条件的记录。通过 INNER JOIN 将 Employees 表与 CTE 连接，确保只更新工资低于 50000 的员工。

注意事项

• CTE 的作用域：CTE 的作用域仅限于紧随其后的单个 SELECT、INSERT、UPDATE 或 DELETE 语句。
• 性能考虑：在大数据集上使用 CTE 进行更新操作时，可能会影响性能。确保在实际使用中进行性能测试和优化。

其他示例

示例 1：更新多个列

假设你还想更新员工的职位（Position 列），可以在 CTE 中选择更多列，并在 UPDATE 语句中进行相应的更新。

-- 定义 CTE
WITH LowSalaryEmployees AS (
    SELECT 
        EmployeeID,
        Salary,
        Position
    FROM 
        Employees
    WHERE 
        Salary < 50000
)
-- 使用 CTE 更新值
UPDATE Employees
SET 
    Salary = Salary * 1.10,
    Position = 'Junior'
FROM Employees e
INNER JOIN LowSalaryEmployees lse
ON e.EmployeeID = lse.EmployeeID;

示例 2：使用 CTE 进行复杂计算

你可以在 CTE 中进行复杂的计算，然后在 UPDATE 语句中使用这些计算结果。

-- 定义 CTE
WITH SalaryCalculations AS (
    SELECT 
        EmployeeID,
        Salary,
        NewSalary = Salary * 1.10
    FROM 
        Employees
    WHERE 
        Salary < 50000
)
-- 使用 CTE 更新值
UPDATE Employees
SET 
    Salary = sc.NewSalary
FROM Employees e
INNER JOIN SalaryCalculations sc
ON e.EmployeeID = sc.EmployeeID;