Delete and subquery : Delete « Insert Update Delete « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Insert Update Delete
  3. Delete
SQL>
SQL>
SQL> create table salary
  2  ( grade      NUMBER(2)   constraint S_PK primary key
  3  , lowerlimit NUMBER(6,2)
  4  , upperlimit NUMBER(6,2)
  5  , bonus      NUMBER(6,2)
  6  , constraint S_LO_UP_CHK check (lowerlimit <= upperlimit)
  7  ) ;

Table created.

SQL>
SQL> insert into salary values (1,  700,1200,   0);

1 row created.

SQL> insert into salary values (2, 1201,1400,  50);

1 row created.

SQL> insert into salary values (3, 1401,2000, 100);

1 row created.

SQL> insert into salary values (4, 2001,3000, 200);

1 row created.

SQL> insert into salary values (5, 3001,9999, 500);

1 row created.

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

1 row deleted.

SQL>
SQL> drop table salary;

Table dropped.








4.4.Delete
4.4.1.The DELETE Statements
4.4.2.Removing Rows Using the DELETE Statement
4.4.3.Delete all rows from a table
4.4.4.Delete with where clause
4.4.5.Writing a DELETE Statement Containing a Subquery
4.4.6.Delete one row
4.4.7.Deleting Row Data
4.4.8.Delete and subquery
4.4.9.To delete the records of emps whose salary is below the average salary in the department (correlated subquery):