8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- SQL for Beginners (Part 10) : The DELETE and TRUNCATE TABLE Statements
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
- DML RETURNING INTO Clause
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:
- 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...