PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle Delete Statements
Version 11.1
 
Basic Delete Statements

Delete All Rows		 DELETE <table_name>
or
DELETE FROM <table_name>;
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 <condition>;
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		 DELETE FROM (<SELECT Statement>);
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>);
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 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
Joins
Nested Tables
Select
Types
Update
Where Clause
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2025 psoug.org
-----