OCP 1Z0 051 164

164. 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. 

该题设计的不好cust_last_name 与 credit_limit （ps:这儿还写错了，应该是 cust_credit_limit）列不能保证数据唯一

SQL> SELECT cust_last_name, cust_credit_limit, COUNT(*) AS ct  2    FROM sh.customers  3   GROUP BY cust_last_name, cust_credit_limit  4  HAVING COUNT(*) > 46;CUST_LAST_NAME                           CUST_CREDIT_LIMIT         CT---------------------------------------- ----------------- ----------Tiffany                                               1500         471 row selected

这时前面用等值判断显然不对,，至于update里的子查询可以看做是可更新view

SQL> UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sh.sales)  2     SET time_id = '22-MAR-2007'  3   WHERE cust_id IN (SELECT cust_id  4                       FROM sh.customers  5                      WHERE cust_last_name = 'Roberts'  6                        AND cust_credit_limit = 600);0 rows updated

Answer: C