从Select语句返回的更新值

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, Name, Salary) VALUES (1, 'John Doe', 50000.00);

-- 使用OUTPUT子句获取更新后的薪水
DECLARE @UpdatedSalaries TABLE (EmployeeID INT, OldSalary DECIMAL(10, 2), NewSalary DECIMAL(10, 2));

UPDATE Employees
SET Salary = Salary * 1.1 -- 增加10%
OUTPUT inserted.EmployeeID, deleted.Salary AS OldSalary, inserted.Salary AS NewSalary INTO @UpdatedSalaries
WHERE EmployeeID = 1;

SELECT * FROM @UpdatedSalaries;

CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    Name VARCHAR(100),
    Salary NUMERIC(10, 2)
);

INSERT INTO Employees (Name, Salary) VALUES ('John Doe', 50000.00);

-- 使用RETURNING子句获取更新后的薪水
WITH updated AS (
    UPDATE Employees
    SET Salary = Salary * 1.1 -- 增加10%
    WHERE EmployeeID = 1
    RETURNING EmployeeID, Salary AS NewSalary, OLD.Salary AS OldSalary
)
SELECT * FROM updated;