Nested Query with UPDATE Statement

The Nested Query can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a sub-query with the UPDATE statement. The basic syntax of Nested query with UPDATE Statement is as follows :
  UPDATE TABLE_NAME SET COLUMN_NAME = VALUE 
          WHERE COLUMN_NAME IN (SELECT COLUMN_NAME FROM TABLE_NAME WHERE [condition]);
For example consider the below table employees.
   ID     NAME        SALARY    
 ------ -----------  ----------
   201    Mukesh       12000     
   202    Dinesh       11900     
   203    Naveen       16000     
   204    Raghav       23000     
   205    Sudeep       13000     
   206    Mahendra      9800      
   207    Neha         14500     
Now we wanted to increment 5% salay of employees whos current salary is less the 15000, then we can do this by the below query :
 SQL> UPDATE EMPLOYEES SET SALARY = SALARY+((SALARY/100)*5) 
                WHERE ID IN (SELECT ID FROM EMPLOYEES WHERE SALARY < 15000);
And the result would be :
   ID    NAME        SALARY    
 ------ ---------  ----------
   201   Mukesh      12600     
   202   Dinesh      12495     
   203   Naveen      16000     
   204   Raghav      23000     
   205   Sudeep      13650     
   206   Mahendra    10290     
   207   Neha        15225     


Next Topic :

No comments:

Post a Comment