Delete

From Oracle FAQ
Jump to: navigation, search

Delete is a SQL DML command used to remove one or more rows from a table. Note that it's important to specify a WHERE-clause, if not ALL rows in the table will be removed.

[edit] Examples

Remove selective rows from a table - in this case only one:

DELETE FROM emp WHERE empno = 7844;
COMMIT;

Remove all rows from a table (also compare to TRUNCATE):

DELETE FROM emp;
ROLLBACK;

Remove records in a specific partition:

DELETE FROM emp_part PARTITION p1;

Remove rows from a remote database (via a database link):

DELETE FROM emp@remote_db;

Remove rows from a SELECT statement:

DELETE FROM (SELECT * FROM emp WHERE deptno = 10);

Delete with RETURNING clause (can only be used from PL/SQL):

DECLARE
  v_rowid urowid; 
BEGIN
  DELETE FROM emp WHERE empno = 7844
  RETURNING rowid INTO v_rowid;
END;
/ 

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #