Nested Query with DELETE Statement

The Nested Query can be used in conjunction with the DELETE statement like with any other statements mentioned above. The basic syntax of Nested Query with DELETE statement is as follows:
 DELETE FROM TABLE_NAME WHERE COLUMN_NAME IN (SELECT COLUMN_NAME FROM TABLE_NAME WHERE [condition]);
For example, consider the below table new_stock :
    ITEM           QUANTITY
 ---------------  ----------
  Mother-Board       15
  RAM                20
  Processor          10
  Monitor            20
  Keyboard           25 
  Mouse              25
  Cabinet            25
Now at the above we need to delete all the items which quantity would be 20 or less then 20, then the Nested query will be
 SQL> DELETE FROM NEW_STOCK WHERE ITEM IN (SELECT ITEM FROM NEW_STOCK WHERE QUANTITY <= 20);
And the result would be :
  ITEM         QUANTITY
-----------  ------------
Keyboard          25
Mouse             25
Cabinet           25

No comments:

Post a Comment