SQL - Update Statements

Scenario: (Uses Oracle-SQL)


HR asks to increment the salary


1. <= 3000, increase by 1000;
2. <= 4000, increase by 500;
3. <= 5000 increase by 250;


Example:
Current Salary: 3000 then New Salary should be 3000 + 1000 + 500 + 250 = 4750


Solution:





create table emp_salary
(
  id               number,
  name             varchar2(20),
  current_sal      number(20,2),
  new_sal          number(20,2)
);

insert into EMP_SALARY (id, name, current_sal)
values (1, 'Mani', 3000);
insert into EMP_SALARY (id, name, current_sal)
values (2, 'abhishek', 4000);
insert into EMP_SALARY (id, name, current_sal)
values (3, 'ajay', 5000);
insert into EMP_SALARY (id, name, current_sal)
values (4, 'arun', 2000);
commit;




1. Not efficient:



update emp_salary
set new_sal = current_sal + 1000 + 500 + 250
where current_sal > 0 and current_sal <= 3000;
update emp_salary
set new_sal = current_sal +  500 + 250
where current_sal >= 3001 and current_sal <= 4000;
update emp_salary
set new_sal = current_sal +  250
where current_sal >= 4001 and current_sal <= 5000;
commit;


2. Efficient:


update test_emp_sal
set current_sal =
   case
      when current_sal > 0 AND current_sal <= 3000 then current_sal + 1000 + 500 + 250
      when current_sal > 3001 AND current_sal <= 4000 then current_sal +  500 + 250
      when current_sal > 4001 AND current_sal <= 5000 then current_sal +  250
   end
;
commit;


==================================================================================

update orders
set order_mode_num =
   decode(order_mode,
             'direct',100,
             'online',200,
             'walmart',300,
             'amazon',400,
          0)
;
==================================================================================

update orders
set order_mode_num =
   case
      when order_mode = 'direct' and
           customer_id < 102 then 500
      when order_mode = 'direct' then 100
      when order_mode = 'online' then 200
      when order_mode = 'walmart' then 300
      when order_mode = 'amazon' then 400
      else 0
   end
;
=================================================================================