本篇将介绍如何在SQL server中使用MERGE, UPDATE, 和DELETE。
同时举具体实例讲解如何使用MERGE, UPDATE, 和DELETE。
首先要确定登陆到指定数据库表格，然后单击New Query。
1. 将HR.Potential_Employees中不存在于HR.Employees中的员工插入到HR.Employees表格中，并且输出插入和删除的数据
USE Sales
GO
MERGE
INTO HR.Employees AS e
USING HR.Potential_Employees AS pe
ON e.EmployeeID = pe.EmployeeID
WHEN MATCHED
THEN UPDATE SET e.Last_Name = pe.Last_Name
e.First_Name = pe.First_Name
WHEN NOT MACHTED BY TARGET
THEN INSERT (EmployeeID, Last_Name, First_Name, DepID)
VALUES (pe.EmployeeID, pe.Last_Name, pe.First_Name, 9)
OUTPUT $action, INSERT.*, DELETE.*;
2. 将删除的数据保存在Empdeleted
USE Sales
DECLARE @EmpDeleted table(
EmployeeID int NOT NULL,
Last_Name nvarchar(50));DELETE FROM HR.Potential_Employees
OUTPUT DELETED.EmployeeID, DELETED.Last_Name
INTO @EmpDeleted;SELECT * FROM @EmpDeleted;
3. 给员工加薪，并且将原始薪水也储存在表格中
USE Sales
CREATE TABLE HR.CommissionUpdate(
SalesEmployeeID int,
OldCommissionPct smallmoney,
NweCommissionPct smallmoney);
UPDATE SaleOrder.SalesEmployee
SET CommissionPct = CommissionPct *1.25
OUTPUT INSERT.SalesEmployeeID, DELETED.CommissionPct,
INSERT.CommissionPct
INTO HR.CommissionUpdate;
4. 查看平均值和登出价格的差价，并查看登出价格大于平均价格的产品
SELECT Product_ID, Name, ListPrice,
(SELECT AVG(ListPrice)
FROM Products.Product_Information)
AS AveragePrice,
ListPrice – (SELECT AVG(ListPrice)
FROM Product.Product_Information)
AS Difference
FROM Products.Product_Information
WHERE ListPrice >(
SELECT AVG(ListPrice)
FROM Products.Product_Information)
5. 查看没有货物还有没有库存
SELECT Name, ReorderPoint
FROM Products.Product_Information AS pi
WHERE EXISTS (
SELECT *
FROM Products.Production AS p
WHERE pi.Product_ID =
p.Product_ID
AND p.StockedQty<pi.RecorderPoint
AND OrderQty = 0)
6. 将价格增加1.2，并且只选择Vendor_ID = 2的产品
UPDATE Products.Product_Information
SET ListPrice = ListPrice +1.2
OUTPUT INSERT.ListPrice AS NEW,
DELETED.ListPrice AS OLD
WHERE Product_ID IN
(SELECT Product_ID
FROM Products.ProductVendor
WHERE Vendor_ID = 2)
