📋Steps in this guide1/6

SQL for Beginners (Part 10) : The DELETE and TRUNCATE TABLE Statements

In this article we take a look at the DELETE and TRUNCATE TABLE Statements.

oracle miscconfigurationintermediate

by OracleDba

14 views

Setup

You can perform all these queries online for free using SQL Fiddle . The examples in this article require the following tables to be present. These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.

Code/Command (click line numbers to comment):

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);

INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;


CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

COMMIT and ROLLBACK

All Data Manipulation Language (DML) changes are done as part of a transaction. They are not permanent until they are confirmed using the statement. Once committed, the only way you can revert a change is to issue a new DML statement to alter the data. Several statements can be grouped together to form a single transaction. Data Definition Language (DDL) commands perform an implicit commit, which also confirms all outstanding DML changes in the current session. If you decide you don't want to keep some uncommitted changes, you can throw them away using using the statement. Many of the examples in this article will issue statements after the test, to revert the data to its original state. Some tools and programming languages default to auto-commit, so they automatically issue a statement after every DML statement they process. Don't let this fool you into thinking this is default behaviour. It is not.

Basic DELETE

The statement is used to remove rows from the table. Without a clause, all rows in the table are deleted by a single statement. The following example deletes all the rows from the EMPLOYEES table, then issues a to cancel the deletion. The clause allows you to limit the rows to be deleted.

Code/Command (click line numbers to comment):

DELETE FROM employees;
14 rows deleted.

SQL>

ROLLBACK;

DELETE FROM employees
WHERE employee_id = 7369;
1 row deleted.

SQL>

ROLLBACK;

DELETE via View

It's possible to delete from the base table associated with a view. There are some restrictions associated with this, but they are a little out of scope for a beginner level article. In the following example, we create a simple view on the EMPLOYEES table, then delete via it. You won't see it very often, but you can also delete via inline views. This can be used to control the number of rows deleted, rather than using a filter in the clause of the statement itself.

Code/Command (click line numbers to comment):

CREATE OR REPLACE VIEW employees_v AS
SELECT * FROM employees;

DELETE FROM employees_v
WHERE employee_id = 7369;

1 row updated.

SQL>

ROLLBACK;

DELETE FROM (SELECT employee_id, salary
             FROM   employees
             WHERE  department_id = 20);

5 rows deleted.

SQL>

ROLLBACK;

0 Rows Deleted

A deletion of zero rows is a valid deletion, and does not result in an error. This can prove rather confusing to beginners. As a result, you can't test for failure to delete rows using the exception in PL/SQL, as it doesn't get raised. Instead, you must manually test for the number of rows deleted using .

Code/Command (click line numbers to comment):

DELETE FROM employees
WHERE  employee_id = 9999;

0 rows deleted.

SQL>

SET SERVEROUTPUT ON
BEGIN
  DELETE FROM employees
  WHERE  employee_id = 9999;

  DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised');
END;
/
NO_DATA_FOUND Not Raised
PL/SQL procedure successfully completed.

SQL>

SET SERVEROUTPUT ON
BEGIN
  DELETE FROM employees
  WHERE  employee_id = 9999;
IF SQL%ROWCOUNT = 0 THEN
    -- Manually raise the NO_DATA_FOUND exception.
    RAISE NO_DATA_FOUND;
  END IF;
DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised');
END;
/
NO_DATA_FOUND Raised
PL/SQL procedure successfully completed.

SQL>

TRUNCATE TABLE

If you want to remove all the rows from a table, the statement is a lot more efficient than the statement. The statement is a DDL command, so it includes an implicit , so there is no way to issue a if you decide you didn't want to remove the rows. In the following example, we check the number of rows in the table, issue the statement, immediately and check the number of rows in the table again. You will see from the output, the does not cancel the statement. The statement can remove the storage associated with the table, or leave it to be reused later. For more information see: - SQL for Beginners (Part 10) : The DELETE and TRUNCATE TABLE Statements - SQL for Beginners - Full Playlist - Oracle SQL Articles - Getting Started - Database SQL Language Reference : DELETE - Database SQL Language Reference : TRUNCATE TABLE - DML RETURNING INTO Clause Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

SELECT COUNT(*)
FROM   employees;

  COUNT(*)
----------
14
1 row selected.

SQL>
TRUNCATE TABLE employees;
Table truncated.

SQL>
ROLLBACK;
Rollback complete.

SQL>

SELECT COUNT(*)
FROM   employees;

  COUNT(*)
----------
0
1 row selected.

SQL>

-- Remove storage.
TRUNCATE TABLE employees;
TRUNCATE TABLE employees DROP STORAGE;

-- Keep storage.
TRUNCATE TABLE employees REUSE STORAGE;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!