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

  1. HOME
  2. Oracle SQL
  3. Data Change Command
  4. DELETE Command

Introduction

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

Demo

SQL>
SQL>--   w  w w .  j  a  va  2  s. com
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