Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?
A.
MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN
MATCHED THEN UPDATE SET c.name = e.first_name ||’, ‘|| e.last_name WHEN NOT MATCHED THEN
INSERT VALUES (e.employee_id, e.first_name ||’, ‘||e.last_name);
B.
MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS
THEN UPDATE SET c.name = e.first_name ||’, ‘|| e.last_name WHEN NOT MATCHED THEN INSERT
VALUES (e.employee_id, e.first_name ||’, ‘||e.last_name);
C.
MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN
EXISTS THEN UPDATE SET c.name = e.first_name ||’, ‘|| e.last_name WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name ||’, ‘||e.last_name);
D.
MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED
THEN UPDATE SET c.name = e.first_name ||’, ‘|| e.last_name WHEN NOT MATCHED THEN INSERT
INTO new_employees VALUES (e.employee_id, e.first_name ||’, ‘||e.last_name);
Explanation:
The correct statement for MERGE isMERGE INTO table_name
Incorrect answer:
B:
Wrong statement with the keyword EXISTS
C:
Wrong statement with the keyword EXISTS
D:
Wrong statement on the MERGE new_employees
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29
The syntax is Mergo INTO
WHEN MATCHED THEN
WHEN UNMATCHED THEN