CODEWITHSUNDEEP
sqloracle11gmerge-statement
Raghav
Raghav

Reputation: 75

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:

Upvotes: 0

Views: 46

Answers (2)

Eid Morsy
Eid Morsy

Reputation: 966

Try this

Update salary 
set salary.salary=salary.salary*(select case t.gender when 'M' then 1.1 When 'F' then 1.15 end from employees t where t.eid= salary.eid)

Upvotes: 0

Avrajit Roy
Avrajit Roy

Reputation: 3303

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) ;

Upvotes: 1

Related Questions