MERGE

这是关于047中的一道MERGE的习题：

79. View the Exhibit and examine the data in ORDERS_MASTER and

MONTHLY_ORDERS tables.

Evaluate the following MERGE statement:

MERGE INTO orders_master o USING  monthly_orders  m

ON (o.order_id = m.order_id)

WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total

DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total);

A. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

(right)

D. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
分析如下：

当 o.order_id=m.order_i d 时，也就是 2 、 3 两条记录 UPDATE SET o.order_total

= m.order_total ，order_id=2记录的 order_total 改成了 2500,order_id=3记录的order_total改为order_total=null，符合 delete 条件，被删

除。目前剩下 1 、 2 、 4


疑问：
DELETE WHERE (m.order_total IS NULL) 但是没说o.order_total is null 删除 orders_master表中的第3行？
实验如下结果：

  ORDER_ID ORDER_TOTAL
---------- -----------
         1        1000
         2        2500
         4


                                                                                                                                                                                                                 good luck

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29674916/viewspace-1781644/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29674916/viewspace-1781644/