OCP 1Z0 051 165

165. View the Exhibit and   examine the description for the CUSTOMERS table. 
You want  to update  the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns  for  the customer 
with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as 
that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as 
that of the customer with CUST_ID 2566. 
Which UPDATE statement will accomplish the task? 

A. UPDATE customers 
SET cust_income_level = (SELECT cust_income_level                          
FROM customers                          
WHERE cust_id = 2560),     
cust_credit_limit = (SELECT cust_credit_limit                          
FROM customers                          
WHERE cust_id = 2566) 
WHERE cust_id=2360; 
B. UPDATE customers 
SET (cust_income_level,cust_credit_limit) = (SELECT                                 
cust_income_level, cust_credit_limit                          
FROM customers                          
WHERE cust_id=2560 OR cust_id=2566) 
WHERE cust_id=2360; 
C. UPDATE customers 
SET (cust_income_level,cust_credit_limit) = (SELECT                                 
cust_income_level, cust_credit_limit                          
FROM customers                          
WHERE cust_id IN(2560, 2566) 
WHERE cust_id=2360; 
D. UPDATE customers 
SET (cust_income_level,cust_credit_limit) = (SELECT                                 
cust_income_level, cust_credit_limit                          
FROM customers                          
WHERE cust_id=2560 AND cust_id=2566) 
WHERE cust_id=2360; 

B C cust_id=2560 OR cust_id=2566 与 cust_id IN(2560, 2566) 结果一样。当然C 还少了半个括号“)”
B 与 C均返回两条记录，一是不符合需求，二是返回两行值让oracle取哪一个，玩儿“你猜”游戏？

SQL> SELECT cust_income_level, cust_credit_limit  2    FROM sh.customers  3   WHERE cust_id = 2560  4      OR cust_id = 2566;CUST_INCOME_LEVEL              CUST_CREDIT_LIMIT------------------------------ -----------------B: 30,000 - 49,999                          3000I: 170,000 - 189,999                        30002 rows selected

SQL> UPDATE sh.customers  2     SET (cust_income_level, cust_credit_limit) =  3         (SELECT cust_income_level, cust_credit_limit  4            FROM sh.customers  5           WHERE cust_id = 2560  6              OR cust_id = 2566)  7   WHERE cust_id = 2360;UPDATE sh.customers   SET (cust_income_level, cust_credit_limit) =       (SELECT cust_income_level, cust_credit_limit          FROM sh.customers         WHERE cust_id = 2560            OR cust_id = 2566) WHERE cust_id = 2360ORA-01427: single-row subquery returns more than one row

D 与 BC相关，用了 and ，结条件变成了什么，2560 = 2566？这当然不会返回值了。

SQL> SELECT COUNT(*)  2    FROM sh.customers  3   WHERE cust_id = 2560  4     AND cust_id = 2566;  COUNT(*)----------         01 row selected

A是对的

SQL> SELECT cust_income_level,  2         (SELECT cust_income_level FROM sh.customers WHERE cust_id = 2560) AS cust_income_level2,  3         cust_credit_limit,  4         (SELECT cust_credit_limit FROM sh.customers WHERE cust_id = 2566) AS cust_credit_limit2  5    FROM sh.customers  6   WHERE cust_id = 2360;CUST_INCOME_LEVEL              CUST_INCOME_LEVEL2             CUST_CREDIT_LIMIT CUST_CREDIT_LIMIT2------------------------------ ------------------------------ ----------------- ------------------G: 130,000 - 149,999           B: 30,000 - 49,999                          5000               30001 row selected

SQL> UPDATE sh.customers  2     SET cust_income_level =  3         (SELECT cust_income_level FROM sh.customers WHERE cust_id = 2560),  4         cust_credit_limit =  5         (SELECT cust_credit_limit FROM sh.customers WHERE cust_id = 2566)  6   WHERE cust_id = 2360;1 row updatedSQL> SELECT cust_income_level, cust_credit_limit  2    FROM sh.customers  3   WHERE cust_id = 2360;CUST_INCOME_LEVEL              CUST_CREDIT_LIMIT------------------------------ -----------------B: 30,000 - 49,999                          30001 row selected

Answer: A