Oracle Delete Statements
Version 18.2.0.1

Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
 
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;

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 12cR2
What's New In 18cR1

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