Term: DELETE

Definition:
In Oracle PL/SQL, the DELETE clause is used to remove data (rows) from a database table. The DELETE statement does not return any rows; in other words, it does not generate a result set. (You can find out how many rows were deleted, but this is not the same as a result set.)

The most basic DELETE syntax looks like this:

DELETE FROM users;

The DELETE statement above will delete all of the rows from the 'users' table because no WHERE clause was used to limit or filter the rows to be matched.

In actual usage you will normally want to delete only table rows that match column values defined in the WHERE clause, as shown in the example below:

DELETE FROM users 

WHERE name = 'john';

The query above deletes only rows where the name column contains only the text 'john'. (It would not, however, remove rows where the name column contained 'john smith'.)

DELETE FROM users 

WHERE name LIKE '%john%';

The query above, which uses the LIKE keyword, would delete all rows where the name column contained the text 'john', as well as rows where the name column contained the text 'john smith', 'Hello john', 'mike johnson', etc.

Note that you can delete all of the rows in a table without deleting the table itself. The table structure, attributes, and indexes will still be intact even after all of the rows are removed from a table. This statement will delete all rows from the table:

DELETE FROM table_name;

However, deleting all rows from a table can be time consuming and resource intensive. In this case it is better to use the TRUNCATE (truncate) command. The TRUNCATE command is much faster than DELETE as it only alters metadata and typically does not expend any overhead enforcing constraints or firing TRIGGERs.

Our thanks to Piyush G for catching a typo on this page.