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


CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [93 users online]    © 2010 psoug.org