Oracle | Delete Statements

Oracle Delete Statements Version 21c

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library

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 ...);