| Library Note |
Morgan's Library Page Header
|
| |
| 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; |
| |
| Basic Delete Statements |
| With multiple duplicate rows in a table delete
all but one of the duplicates |
DELETE FROM airplanes a1
WHERE rowid <> (
SELECT MIN(rowid)
FROM airplanes a2
WHERE a2.col1 = a1.col1
AND a2.col2 = a1.col2
AND ...); |