sqloracle11gmerge-statement

Update records of one table using data from another table


I've two tables.

Table 1: Employees

EID Name    Gender
1   Peter   M
2   John    M
3   Melissa F

Table 2: Salary

EID Salary
1   6000
2   8000
3   10000

I need to raise salary of male employees by 10% and Female employees by 15%.

Below is the query that I've used but can't achieve required result in Oracle11g.

merge into salary
using employees on 
salary.eid = employees.eid
when matched then
update set
    salary.salary = 1.1*salary where employee.gender = 'M' ,
    salary.salary = 1.15*salary where employee.gender = 'F';

I got below error message:

SQL Error: ORA-00969: missing ON keyword 00969. 00000 - "missing ON keyword" *Cause:
*Action:


Solution

  • There are two things which you need to consider in the snippet provided.

    1. ON clause should always be accompanied by "()".
    2. WHERE clause in UPDATE statement is not correct. Hope this snippet helps.

      MERGE INTO SALARY USING EMPLOYEES 
      ON (salary.eid = employees.eid)
      WHEN MATCHED THEN
        UPDATE
        SET salary.salary = DECODE(employee.gender,'M',1.1*salary,'F',1.15*salary) ;