update the salary base on onther table

Question

I have two tables first one is emp(emp_id,name,gender--M or F) and second one is emp_sal(emp_id,address,salary).I want to update salary filed like female employee should be increment 10% and male employee should be increment 15% of current salary

Answer 1

We can try with the below query.....

UPDATE  empd es set es.E_salary=case 
when(select eg.gender from emps eg where eg.empid=es.empid)='M'
THEN 
es.E_salary*0.1
else
es.E_salary*0.5
end;

Answer 2

DECLARE
CURSOR cur_emp
IS
SELECT * FROM emp;
rec_emp cur_emp%rowtype;
BEGIN
   OPEN cur_emp;
   LOOP
FETCH cur_emp INTO rec_emp;
EXIT WHEN cur_emp%notfound; 
IF rec_emp.gender = 'F'THEN
  UPDATE emp_sal SET sal = sal + (sal * 0.1) WHERE emp_id = rec_emp.emp_id;
elsif rec_emp.GENDER = 'M' THEN
  UPDATE emp_sal SET sal = sal + (sal * 0.15) WHERE emp_id = rec_emp.emp_id;
END IF;
END LOOP;
CLOSE cur_emp;