TRUNCATE DROP AND DELETE SQL Statement

TRUNCATE SQL Statement

TRUNCATE is a DDL command. It performs the same function as a DELETE statement without a WHERE clause. TRUNCATE is executed using a table lock and whole table is locked for remove all records. This statement mostly used to delete the table data permanently. Truncate statement deletes the storage occupied by the table. The deleted storage can be used by the table in future. We cannot use Where clause with TRUNCATE. The truncate statement can’t be rolled back also we can’t fire any trigger. To truncate a table, the table must be in our schema or we must have DROP ANY TABLE system privilege. Truncate statement is faster and does not use undo as delete statement. In truncate there is minimal logging in transaction log, so it is performance wise faster. De-allocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter. Also Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process. To use Truncate on a table you need at least ALTER permission on the table. Truncate uses the less transaction space than Delete statement. But truncate cannot be used with indexed views.

Syntax

TRUNCATE TABLE TABLE_NAME;

Example

Let us have a look at below STUDENTS_COPY table.

SELECT * FROM STUDENTS_COPY;

TRUNCATE TABLE STUDENTS_COPY;

SELECT * FROM STUDENTS_COPY;

Now the all the records of STUDENTS_COPY table are removed, but table structure is present.

Preserving Materialized View Logs After Truncate

TRUNCATE TABLE TABLE_NAME PRESERVE MATERIALIZED VIEW LOG;

DELETE SQL Statement

DELETE is a DML command. DELETE is executed using a row lock, each row in the table is locked for deletion. We can use where clause with DELETE to filter & delete specific records. It maintains the log, so it is slower than TRUNCATE. Delete uses the more transaction space than TRUNCATE statement. To use Delete you need DELETE permission on the table. It can be used with indexed views.

Syntax

DELETE FROM TABLE_NAME

[WHERE CONDITION];

Have a look at our previous post on DELETE SQL query. TRUNCATE is DDL statement which implicitly commits. If you are not sure of the action you do in a schema, and want to commit only after a manual verification, then do not TRUNCATE, use DELETE instead.

DROP SQL Statement

DROP is a DDL command. The DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints, permission specifications for that table and indexes of related objects.Drop statement is an irreversible statement. the operation cannot be rolled back. When we execute the DROP statement then no DML triggers will be fired.

Syntax

DROP TABLE TABLE_NAME;

Example

Let us DROP STUDENTS_COPY by using DROP statement.

DROP TABLE STUDENTS_COPY;

SELECT * FROM STUDENTS_COPY;

Table does not exist after drop statement.

DELETE a row from table SQL query

The SQL DELETE STATEMENT

The DELETE statement is used to delete existing records in a table. The DELETE statement does not return any rows; that is, it will not generate a result set. Executing a DELETE statement can cause triggers to run that can cause deletes in other tables. For example, if two tables are linked by a foreign key and rows in the referenced table are deleted, then it is common that rows in the referencing table would also have to be deleted to maintain referential integrity. We can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

Syntax

DELETE FROM TABLE_NAME

[WHERE CONDITION];

Example

Let us see all the records of STUDENTS_COPY table first.

SELECT * FROM STUDENTS_COPY;

The following SQL statement deletes the student   "Oliver Queen" from the STUDENTS_COPY  table.

DELETE FROM STUDENTS_COPY

WHERE ROLLNO=4;

COMMIT;

We can see below that the record for the Oliver Queen is not present in the table.

SELECT * FROM STUDENTS_COPY;

Example

We can delete all rows in a table without deleting the table, which means that the table structure, attributes, and indexes will be not altered.

DELETE FROM STUDENTS_COPY;

COMMIT;

SELECT * FROM STUDENTS_COPY;

Example using EXIST clause

When we want to delete records in one table based on values in another table. Since we cannot list more than one table in the FROM clause while performing a delete, there we can use the EXIST clause.

Now we will delete record from STUDENTS_COPY table based on condition met on MARKS table.

SELECT * FROM MARKS;

SELECT * FROM STUDENTS_COPY;

DELETE FROM STUDENTS_COPY

WHERE EXISTS

  (SELECT *

   FROM MARKS

   WHERE MARKS.ROLLNO = STUDENTS_COPY.ROLLNO

   AND MARKS.ROLLNO =12);

SELECT * FROM STUDENTS_COPY;

We can see that the record for "Leonard Snart" is removed from the STUDENTS_COPY table based on ROLLNO.  mentioned in where clause on MARKS table.