Oracle | Delete Statements

Oracle Delete Statements Version 21c

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Basic Delete Statements

Delete All Rows DELETE [<schema_name>.]<table_name> or DELETE FROM [<schema_name>.]<table_name>;

conn uwclass/uwclass@pdbdev

      

      CREATE TABLE t AS

      SELECT *

      FROM all_tables;

      

      SELECT COUNT(*)

      FROM t;

      

      DELETE FROM t;

      

      COMMIT;

      

      SELECT COUNT(*)

      FROM t;

Delete Selective Rows DELETE FROM [<schema_name>.]<table_name> WHERE <filter_condition>;

conn uwclass/uwclass@pdbdev

      

      CREATE TABLE t AS

      SELECT *

      FROM all_tables;

      

      SELECT COUNT(*)

      FROM t;

      

      DELETE FROM t

      WHERE table_name LIKE '%MAP';

      

      COMMIT;

      

      SELECT COUNT(*)

      FROM t;

Delete From A SELECT Statement: May be Selective or Non-Selective Depending on whether the SELECT statement contains a WHERE clause DELETE FROM (<SELECT Statement>);

conn uwclass/uwclass@pdbdev

      

      CREATE TABLE t AS

      SELECT *

      FROM all_tables;

      

      SELECT COUNT(*)

      FROM t;

      

      DELETE FROM (SELECT * FROM t WHERE table_name LIKE '%MAP');

      

      SELECT COUNT(*)

      FROM t;

Delete With Returning Clause DELETE FROM (<SELECT Statement>);

conn uwclass/uwclass@pdbdev

      

      CREATE TABLE t AS

      SELECT *

      FROM all_tables;

      

      set serveroutput on

      

      DECLARE

       r  urowid;
      

      BEGIN

        DELETE FROM t

        WHERE rownum = 1

        RETURNING rowid  INTO r;

      

        dbms_output.put_line(r);

      END;

      /

Delete Restricted to a Partition in a Partitioned Table DELETE FROM [<schema_name>.]<table_name> PARTITION (<partition_name>);

DELETE FROM sales PARTITION (q1_2001_invoices);

Delete from a Remote Database DELETE FROM [<schema_name>.]<table_name>@<database_link>

DELETE FROM t@remote_db;

Basic Delete Statements

With multiple duplicate rows in a table delete all but one of the duplicates DELETE FROM airplanes a1 WHERE rowid <> ( SELECT MIN(rowid) FROM airplanes a2 WHERE a2.col1 = a1.col1 AND a2.col2 = a1.col2 AND ...);