Oracle SQL - Using Subquery in the WHERE Clause of DELETE Command

Introduction

The following code shows how to use alternative DELETE Command: a Subquery in the WHERE Clause.

Demo

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;

Table created.-- w w  w .  j  a va 2s. c o  m

SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL>
SQL> DELETE FROM departments
  2    WHERE dname = (SELECT dname
  3                         FROM departments
  4                       WHERE location = 'DALLAS');

1 row deleted.
SQL>
SQL> select * from departments;

    DEPTNO DNAME      LOCATION        MGR
---------- ---------- -------- ----------
        10 ACCOUNTING NEW YORK       7007
        30 SALES      CHICAGO        7006
        40 HR         BOSTON         7009

SQL>

Related Topic