In this article, we will discover the syntax of the DELETE and TRUNCATE commands.. Also, we will find out the difference between these two commands.
The Oracle DELETE command syntax
This command allows us to delete one specific row (or more) from our table based on a condition. We can also delete all the rows from our table using this command.
The syntax of this command is:
DELETE FROM our_table_name WHERE our_condition;
- our_table_name is the table name we want to delete data in.
- our_condition is what condition the row must meet to be deleted.
Note: The condition is optional and we don’t have to specify it.
The Oracle TRUNCATE command syntax
Using this command, we can delete all the records from our Oracle table at once without the condition specification.
The syntax is:
TRUNCATE TABLE [schema.] our_table_name [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ] [ DROP STORAGE | REUSE STORAGE ] ;
- schema is the name of the schema our table is related to.
- our_table_name is the table name we want to truncate data in.
- MATERIALIZED VIEW LOG option means that the materialized view log will be saved or cleaned after the table is truncated.
- STORAGE option means that we can deallocate or remain located the storage used for rows of the table.
TRUNCATE vs. DELETE
DELETE command is good for deleting some specific rows of the table which meets the conditions. When we need to delete all the table contents, it is better to use the command that exists specifically for this, the TRUNCATE command.
|It locks the rows when they are assigned for deletion.||Locks the table instead of separate rows.|
|We can specify the requirements for deletion using the WHERE clause.||We cannot specify the conditions for deletion, and thus, we can only delete all rows from the table.|
|It writes down all the actions to the log, so it acts slower. Deletes the rows one by one and writes every action to the log. This is why it uses more space in the log.||Acts faster, as it uses less space in the log. Deletes data, freeing up data pages used to store table data. Only freed pages are written to the log.|
|Before deleting something in the table we need to have the DELETE permission to do this.||ALTER permission is needed.|
|Works with indexed views||Doesn’t work with indexed views|
Table 1. DELETE and TRUNCATE difference
Let’s create a sample table to test the DELETE and TRUNCATE commands.
The script will be:
CREATE TABLE SomeWeek( DayId INTEGER PRIMARY KEY, Day_of_the_week VARCHAR (10) NOT NULL);
And also we will add some records:
INSERT INTO SomeWeek VALUES (1, 'Monday'); INSERT INTO SomeWeek VALUES (2, 'Tuesday'); INSERT INTO SomeWeek VALUES (3, 'Wednesday'); INSERT INTO SomeWeek VALUES (4, 'Thursday'); INSERT INTO SomeWeek VALUES (5, 'Friday'); INSERT INTO SomeWeek VALUES (6, 'Saturday'); INSERT INTO SomeWeek VALUES (7, 'Sunday');
Figure 1. “SomeWeek” table
To get rid of all the records with the DELETE command in our table, we will use a script:
DELETE FROM SomeWeek;
Figure 2. Deleting the rows from “SomeWeek” table
To get rid of specific rows using the DELETE command, we will use a script:
DELETE FROM SomeWeek WHERE DayId = 2;
Figure 3. Deleting a specific row from “SomeWeek” table
To clear all the records in our table with the TRUNCATE table command, we will use the script:
TRUNCATE TABLE SomeWeek;
Figure 4. Truncating the “SomeWeek” table
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.