8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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

This is the tenth part of a series of articles showing the basics of SQL. In this article we take a look at the DELETE and TRUNCATE TABLE Statements.

Related articles.

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.

--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;

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.

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 COMMIT 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 ROLLBACK statement. Many of the examples in this article will issue ROLLBACK 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 COMMIT 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 DELETE statement is used to remove rows from the table. Without a WHERE 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 ROLLBACK to cancel the deletion.

DELETE FROM employees;

14 rows deleted.

SQL>

ROLLBACK;

The WHERE clause allows you to limit the rows to be deleted.

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.

CREATE OR REPLACE VIEW employees_v AS
SELECT * FROM employees;

DELETE FROM employees_v
WHERE employee_id = 7369;

1 row updated.

SQL>

ROLLBACK;

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 WHERE clause of the DELETE statement itself.

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.

DELETE FROM employees
WHERE  employee_id = 9999;

0 rows deleted.

SQL>

As a result, you can't test for failure to delete rows using the NO_DATA_FOUND exception in PL/SQL, as it doesn't get raised.

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>

Instead, you must manually test for the number of rows deleted using SQL%ROWCOUNT.

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 TRUNCATE TABLE statement is a lot more efficient than the DELETE statement. The TRUNCATE TABLE statement is a DDL command, so it includes an implicit COMMIT, so there is no way to issue a ROLLBACK 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 TRUNCATE TABLE statement, immediately ROLLBACK and check the number of rows in the table again. You will see from the output, the ROLLBACK does not cancel the TRUNCATE TABLE statement.

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>

The TRUNCATE TABLE statement can remove the storage associated with the table, or leave it to be reused later.

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

-- Keep storage.
TRUNCATE TABLE employees REUSE STORAGE;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.