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

  1. HOME
  2. Oracle SQL
  3. Data Change Command
  4. 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.--   www.  j  av  a 2 s  .c  om

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