Update salary of newly transferred employee from EMPLOYEE table to EMP30 table with MERGE statement, and INSERT employees who are not in EMP30 table.

Question

Use the N2 Corporation database tables to design the following subqueries. (Use the spooling method to capture all queries and results in the CHAP8SP1.LST file.)

Answer 1

```
SQL> MERGE INTO emp30 o
2 USING (SELECT EmployeeId, Lname, Fname, HireDate, Salary
3 FROM employee) e
4 ON (o.EmployeeId = e.EmployeeId)
5 WHEN MATCHED THEN
6 UPDATE SET o.Salary = e.Salary
7 WHEN NOT MATCHED THEN
8 INSERT (o.EmployeeId,o.Lname,o.Fname,o.HireDate,o.Salary)
9 VALUES (e.EmployeeId,e.Lname,e.Fname,e.HireDate,e.Salary);

8 rows merged.

SQL> SELECt * FROM emp30;

EMPLOYEEID LNAME FNAME HIREDATE SALARY
---------- --------------- --------------- --------- ----------
200 Shaw Jinku 03-JAN-00 24500
135 Garner Stanley 29-FEB-96 45000
222 Chen Sunny 15-AUG-99 35000
111 Smith John 15-APR-60 265000
123 Roberts Sandi 02-DEC-91 75000
543 Dev Derek 15-MAR-95 80000
433 McCall Alex 10-MAY-97 66500
246 Houston Larry 19-MAY-67 150000

8 rows selected.
```