Oracle Delete Statements
Version 11.2.0.3

Basic Delete Statements
Delete All Rows DELETE <table_name>
or
DELETE FROM <table_name>;
conn uwclass/uwclass

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

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

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

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
Nested Tables
Oracle Built-in Functions
Partitioned Tables
SELECT Statements
Types
UPDATE Statements
WHERE Clause

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-2013 Daniel A. Morgan All Rights Reserved