Oracle SQL - Using subqueries in the FROM clause of the DELETE statement

Introduction

The following code illustrates how to use subqueries in the FROM clause of the DELETE statement.

Demo

SQL>
SQL>-- from w w  w  .ja  va  2 s.  c  o m
SQL> drop table salgrades;

Table dropped.

SQL> create table salgrades(
  2  grade      NUMBER(2)   primary key,
  3  lowerlimit NUMBER(6,2) not null check (lowerlimit >= 0),
  4  upperlimit NUMBER(6,2) not null,
  5  bonus      NUMBER(6,2) not null);
SQL>
SQL> insert into salgrades values (1,  700,1200,   0);
SQL> insert into salgrades values (2, 1201,1400,  50);
SQL> insert into salgrades values (3, 1401,2000, 100);
SQL> insert into salgrades values (4, 2001,3000, 200);
SQL> insert into salgrades values (5, 3001,9999, 500);
SQL>
SQL> select *
  2  from salgrades;

    GRADE | LOWERLIMIT | UPPERLIMIT |     BONUS
--------- | ---------- | ---------- | ---------
 00001.00 |   00700.00 |   01200.00 |  00000.00
 00002.00 |   01201.00 |   01400.00 |  00050.00
 00003.00 |   01401.00 |   02000.00 |  00100.00
 00004.00 |   02001.00 |   03000.00 |  00200.00
 00005.00 |   03001.00 |   09999.00 |  00500.00

SQL>
SQL> delete from (select *
  2               from   salgrades
  3               where  grade = 5);

1 row deleted.

SQL>
SQL>
SQL> select *
  2  from salgrades;

    GRADE | LOWERLIMIT | UPPERLIMIT |     BONUS
--------- | ---------- | ---------- | ---------
 00001.00 |   00700.00 |   01200.00 |  00000.00
 00002.00 |   01201.00 |   01400.00 |  00050.00
 00003.00 |   01401.00 |   02000.00 |  00100.00
 00004.00 |   02001.00 |   03000.00 |  00200.00

SQL>

Related Topic