WHEN NOT MATCHED THEN UPDATE SET
.....
WHEN MATCHED THEN INSERT
2、UPDATE和INSERT子句可以加WHERE子句
现在由于需求改变，我们仅仅需要把员工1001的订单数据同步到订单历史记录表
MERGE INTO ORDER_HISTORY H USING
(
SELECT ORDER_ID ,--订单编号
CUSTOMER_ID ,--客户编号
EMPLOYEE_ID ,--员工编号
ORDER_DATE ,--订购日期；
REQUIRED_DATE ,--预计到达日期
SHIPPED_DATE ,--发货日期
SHIPPER ,--运货商
FREIGHT ,--运费
SHIP_NAM ,--货主姓名；
SHIP_ADDRESS ,--货主地址
SHIP_CITY ,--货主所在城市；
SHIP_REGION ,--货主所在地区；
SHIP_POSTALCODE ,--货主邮编
SHIP_COUNTRY --货主所在国家
FROM ORDER_DTL
) O
ON
(
O.ORDER_ID = H.ORDER_ID
)
WHEN MATCHED THEN UPDATE SET
H.CUSTOMER_ID = O.CUSTOMER_ID ,
H.EMPLOYEE_ID = O.EMPLOYEE_ID ,
H.ORDER_DATE = O.ORDER_DATE ,
H.REQUIRED_DATE = O.REQUIRED_DATE ,
H.SHIPPED_DATE = O.SHIPPED_DATE ,
H.SHIPPER = O.SHIPPER ,
H.FREIGHT = O.FREIGHT ,
H.SHIP_NAM = O.SHIP_NAM ,
H.SHIP_ADDRESS = O.SHIP_ADDRESS ,
H.SHIP_CITY = O.SHIP_CITY ,
H.SHIP_REGION = O.SHIP_REGION ,
H.SHIP_POSTALCODE = O.SHIP_POSTALCODE ,
H.SHIP_COUNTRY = O.SHIP_COUNTRY
WHERE O.EMPLOYEE_ID = '1001'
WHEN NOT MATCHED THEN INSERT
(
H.ORDER_ID ,
H.CUSTOMER_ID ,
H.EMPLOYEE_ID ,
H.ORDER_DATE ,
H.REQUIRED_DATE ,
H.SHIPPED_DATE ,
H.SHIPPER ,
H.FREIGHT ,
H.SHIP_NAM ,
H.SHIP_ADDRESS ,
H.SHIP_CITY ,
H.SHIP_REGION ,
H.SHIP_POSTALCODE ,
H.SHIP_COUNTRY
)
VALUES
(
O.ORDER_ID ,
O.CUSTOMER_ID ,
O.EMPLOYEE_ID ,
O.ORDER_DATE ,
O.REQUIRED_DATE ,
O.SHIPPED_DATE ,
O.SHIPPER ,
O.FREIGHT ,
O.SHIP_NAM ,
O.SHIP_ADDRESS ,
O.SHIP_CITY ,
O.SHIP_REGION ,
O.SHIP_POSTALCODE ,
O.SHIP_COUNTRY
) WHERE O.EMPLOYEE_ID = '1001'；