Oracle SQL - Data Change Command DELETE Command

Introduction

DELETE command operates at the table level, and you use the WHERE clause to restrict the set of rows to delete.

If you omit the WHERE clause, the DELETE command results in an empty table.

Consider the following two commands:

drop  table departments
delete from departments

The DROP TABLE command not only removes the contents of the table, but also the table itself, including all dependent objects/structures such as indexes and privileges.

DROP TABLE is a data definition (DDL) command.

The DELETE command does not change the database structure, but only the contents-it is a data manipulation (DML) command.

A DROP TABLE command cannot be undone with a ROLLBACK command, as opposed to the effects of a DELETE command, which can.

Example of a DELETE Command

Demo

SQL>
SQL>-- from   w w  w  . j  a  va  2 s . co  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 salgrades
  2  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 Topics