Oracle Delete Statements
Version 12.2.0.1

Library Note Morgan's Library Page Header
"We don't live in a dictatorship or a monarchy. I swore an oath in the military and in the Senate to preserve, protect and defend the Constitution of the United States, not to mindlessly cater to the whims of Cadet Bone Spurs and clap when he demands I clap,"
~ Sen. Tammy Duckworth
 
Basic Delete Statements
Delete All Rows DELETE <table_name>
or
DELETE FROM <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 <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 <table_name>
PARTITION (<partition_name>);
DELETE FROM sales PARTITION (q1_2001_invoices);
Delete from a Remote Database DELETE FROM <table_name>@<database_link>
DELETE FROM t@remote_db;

Related Topics
Conditions
Database Link
Error Logging
INSERT Statements
Joins
MERGE Statements
Nested Tables
Object Tables
Oracle Built-in Functions
Partitioned Tables
SELECT Statements
Types
UPDATE Statements
WHERE Clause
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved