Delete database records

It is certainly important to understand that this article, Deleting database records, will deal with content about SQL language – DELETE, more specifically how to delete records within a database table.

First, when it comes to changing records in a Database (DB) it is important to review two concepts: creating tables, inserting records and changing records. Therefore, it is recommended to read the articles: Insert database records, and Update database records.

Thus, to facilitate, the same table and records used in the two articles mentioned above will be used.

DELETE syntax

Finally, you can now move on to the DELETE command syntax. Just to illustrate, as shown below, DELETE has as essential components of its syntax, the table_name, and the clause that makes it possible to apply filter criteria.

DELETE 
  FROM table_name
[WHERE]
   [filter_criteria];

On the other hand, it is important to point out some aspects about the application of this command:

• A DELETE command applies the deletion of record(s) in a single table.
• A DELETE command can delete none, one, or multiple rows of records.
• It can be applied without the WHERE clause, but this way it will affect all rows of records in the table (for example, if the table has a thousand records, the thousand will be deleted).
• The WHERE clause filters the rows of records that will be deleted in a DELETE command.

Practical Experiment

To generate the practical experiment, the PESSOA table and its records inserted in the article Insert ORACLE database records will be used. However, to make it easier, below is an illustration of the table with its records.

PESSOA = PERSON. DATA_NASCIMENTO = DATE_BIRTH. NOME = NAME. GENERO = GENDER.

In the same way, this practical experiment can be applied through a virtual machine, or a normal machine that has an ORACLE version installed (it can be the Express Edition version).

Or, you can use Oracle Live SQL, an online version via browser for testing provided by Oracle itself (you just need to have a valid account on their website). This article will use the Oracle Live SQL online tool to carry out practical experiments.

Applying DELETE Commands

DELETE FROM PESSOA 
 WHERE ID = 1;
-- 1 row(s) deleted.
DELETE FROM PESSOA;
-- 2 row(s) deleted.
DELETE FROM PESSOA 
 WHERE GENERO = 'F';
-- 1 row(s) deleted.
DELETE FROM PESSOA 
 WHERE ID = 1 AND GENERO = 'F';
-- 1 row(s) deleted.

Namely, all values related to text fields, such as the NOME and GENERO columns must be enclosed in apostrophes. On the other hand, to accurately pass the date to ORACLE, it is important to use the TO_DATE function.

Function that converts text enclosed in apostrophes into a date in the following format, as in the DATA_NASCIMENTO field.

Explanation of SCRIPT above

The DELETE of line 1:

• Uses the primary key to delete only the specific record.

The DELETE of line 4:

• Deletes all rows of records from the table PESSOA. Applying the command without WHERE erases all rows of records.

The DELETE of line 6:

• Deletes all rows of records that have the value F (female) in the field GENERO.

The DELETE of line 9:

• Deletes all records that have the value 1 for the field ID and (AND), the value F in the field GENERO. That is, it is possible to use filter composition in the WHERE clause.

Features

Reinforcing the features of the command:

• Delete only records from a single table.
• It is not necessary to inform columns, because when applying the DELETE command it affects the entire row, regardless of the column.
• In transactional databases, DELETEs are only effective using COMMIT, if you do not want them to be effective, you can cancel with ROLLBACK.
• Date field, or date time, use the TO_DATE function to inform value (this prevents date inversion due to American or Latin format).

Good habits

• As a good practice, you should not apply a DELETE without the WHERE clause:
  • Because you can delete all rows of records.
  • After applying a COMMIT to effect this DELETE, it would not be possible to return the previous values.
  • This recovery being very complicated (in some cases only being possible with the BACKUP of the database; and even then with loss of some values).
  • There are risks even with BACKUP of not being able to return all values.
• Another good practice is to apply a SELECT with the same WHERE clause that the DELETE will be applied to, to check the number of records that can suffer the action of the command.
  • This check before applying the DELETE can indicate if something is not expected.
  • Example: I was supposed to delete a row, but the check SELECT is showing 3 rows that can be affected with this WHERE.
  • This indicates that the WHERE should be rechecked, there may be an error, a missing filter, etc.
  • Remembering, the SELECT command does not change the records, it is a record view feature.
• The ideal is to apply a DELETE command using the primary key(s) of the table, this guarantees that only one record will be deleted. This ensures in a way, not to delete records by accident. Although the previous tip helps this not happen.

Conclusion

Anyway, deleting records from a database is the important step related to records in a table. Therefore, the focus of this article was to present in a simple and practical way the subject of deleting records in an ORACLE database table. However, the use of a methodology based on practical experiments aimed at a problem helps in understanding and facilitates further deepening of the topic. To see more articles related to Database, Click here.