OCP-1Z0-051 第164题 UPDATE语句中使用子查询

一、原题

View the Exhibit and examine the structure of CUSTOMERS and SALES tables.

Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales)
   SET time_id = '22-MAR-2007'
 WHERE cust_id = (SELECT cust_id
                    FROM customers
                   WHERE cust_last_name = 'Roberts'
                     AND credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?
A. It would not execute because two tables cannot be used in a single UPDATE statement.
B. It would not execute because the SELECT statement cannot be used in place of the table name.
C. It would execute and restrict modifications to only the columns specified in the SELECT statement.
D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

答案：C

二、题目翻译

看下面的CUSTOMERS and SALES表的结构，
评估下面的SQL语句：
关于执行上面的UPDATE语句哪句话是正确的？
A.执行失败，因为两个表不能用于一个UPDATE语句。
B.执行失败，因为SELECT语句不能用于代替表名。
C.执行成功，并限制只能修改SELECT语句指定的列。
D.失败失败，因为子查询不能用于UPDATE的WHERE子句中。

三、题目解析

UPDATE后面的SELECT语句相当于一个简单视图，也就是对这个视图进行修改，所以，只能修改select后面的的列。