修改数据

　　我们一般使用UPDATE语句对表中数据行的一个子集进行修改，除了使用标准的UPDATE语句外，我们还可以使用基于联接的UPDATE语句。为了测试，我们先创建了两张表，Orders和OrderDetails，测试代码如下。

USE tempdb;
GO

-- 准备测试数据
IF OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.OrderDetails','U') IS NOT NULL DROP TABLE dbo.OrderDetails;
GO

SELECT * INTO dbo.Orders FROM TSQLFundamentals2008.Sales.Orders;
SELECT* INTO dbo.OrderDetails FROM TSQLFundamentals2008.Sales.OrderDetails;
GO

ALTER TABLE dbo.Orders ADD
CONSTRAINT PK_Orders PRIMARY KEY(orderid);

ALTER TABLE dbo.OrderDetails ADD
CONSTRAINT PK_OrderDetails PRIMARY KEY(orderid,productid),
CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY(orderid) REFERENCES dbo.Orders(orderid);

-- 更新数据
-- 方法一：UPDATE
UPDATE dbo.OrderDetails SET discount+=0.05
WHERE productid=51;

-- 基于联接的UPDATE
UPDATE orderdetails SET discount+=0.05
FROM dbo.OrderDetails AS orderdetails
LEFT JOIN dbo.Orders AS orders ON orderdetails.orderid = orders.orderid
WHERE orders.custid=1;

-- 使用OUTPUT输出修改前数据
UPDATE dbo.OrderDetails SET discount+=0.05
OUTPUT
    INSERTED.productid,DELETED.discount AS OldDiscount,INSERTED.discount AS NewDiscount
WHERE productid=51;