Blog

Using the DELETE FROM Statement

In SQL database management systems, the DELETE FROM command statement is used to delete one or more table rows from a database table based on a particular logical expression condition. Remember, DELETE FROM is a powerful and risky command statement and should be used carefully by new database users. This is especially true when the WHERE clause statement is omitted, as it will delete all rows in your existing table at once.

Syntax of the DELETE FROM statement in a database system.

The basic syntax of the DELETE FROM statement in an SQL database management system is as follows:

DELETE FROM table_name

WHERE condition;

Elements of the DELETE FROM statement.

• table_name – Here, the table name is the name of the table from which the database user wants to delete table rows.
• condition – WHERE is a logical database table condition that indicates to the database user which table rows should be deleted in the existing database. If the user-defined condition is met, the specific table rows are deleted.

Important considerations for the DELETE FROM statement.

• WHERE clause – In SQL database tables, the WHERE clause is very important for controlling which table rows are deleted, as it allows deletion based on a user-defined WHERE condition. Remember, without a WHERE clause, all rows in the table can be deleted.
• Transaction control – If your installed database software supports database transactions, such as MySQL, PostgreSQL, SQL Server, etc., you can use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements or features to manage unwanted table modifications and prevent accidental user deletion activities.

Example of using the DELETE FROM statement.

Deleting a single row in an SQL database table.

To delete a single row in an SQL database table, the database user can create a condition that uniquely identifies a row in the table.

Example of deleting a single row in a database.

DELETE FROM employe

WHERE employee_id = 101;

In this example.

• the row in the `employe` table where `employee_id` is equal to 101 is immediately deleted from the table.

Deleting multiple rows in an SQL database table based on a condition.

In an SQL database table, a database user can create a condition to delete multiple table rows simultaneously, which matches multiple records in a database table.

Example of deleting multiple rows.

DELETE FROM employe

WHERE department = ‘Development’;

In this example.

• all rows where the department is ‘Development’ are deleted from the `employe` table.
• If the condition matches more than one table row, multiple table rows can be deleted at the same time.

Deleting all rows of an SQL database table.

In an SQL database table, if the user ignores the WHERE clause condition expression, all rows of the existing database table will be deleted simultaneously. This is a very risky database operation because it deletes all rows of the existing database table, but the structure of the existing table remains the same; only the table row records are removed.

Example of deleting all rows of a database table.

DELETE FROM employe;

In this example.

• all rows of the `employe` table are deleted. While the table structure still exists, it now contains no table records or rows.

Deleting rows with multiple conditions in an SQL database.

In an SQL database table, a database user can use logical operators such as AND, OR, or NOT to delete table rows that fulfill multiple conditions at the same time.

Example of deleting rows with multiple conditions.

DELETE FROM employe

WHERE department = ‘Design’ AND age < 40;

In this example.

• In this example, all employees in the Design department whose age is less than 40 will be deleted.

Deleting rows using IN in an SQL database table.

The IN operator in an SQL database table helps the database user control or specify a list of values, and any rows in the table that match any of these values ​​will be deleted from the table.

Example of deleting rows using IN.

DELETE FROM employee

WHERE department IN (‘Design’, ‘Development’);

In this example.

• all employees from the Design and Development departments are deleted from the employee table.

Deleting rows using LIKE in an SQL database table.

The LIKE operator in an SQL database table allows pattern matching in string columns. Database users can use it to delete table rows that match a particular pattern.

Example of deleting rows using LIKE.

DELETE FROM employee

WHERE emp_name LIKE ‘%B’;

In this example.

• this deletes all employees from the current employee table whose emp_name starts with the character ‘B’.

Deleting Rows with IS NULL in SQL Database Tables.

In SQL database tables, database users can apply the IS NULL condition to delete table rows where a NULL value is defined in a table column.

Example of Deleting Rows with IS NULL.

DELETE FROM employe

WHERE hire_date IS NULL;

In this example.

• all table rows are deleted from the ’employe’ table where the employee’s ‘hire_date’ is defined as NULL.

Using Transactions with DELETE in SQL Database Tables.

If your database system fully supports transaction command statements, database users can use command features such as BEGIN TRANSACTION, COMMIT, and ROLLBACK to ensure that the deletion process in the existing table is performed securely and can be easily undone if necessary.

Example of Transactions with DELETE.

Using transactions with DELETE in SQL database tables.

BEGIN TRANSACTION;

DELETE FROM employe

WHERE department = ‘Development’;

— Check here to ensure everything is correct before committing to the database.

— If there is a mistake in this operation, the user can roll it back.

COMMIT;

In this example.

• the deletion of employees from the Development department is wrapped in a transaction.
• If you need to undo your modifications, you can use the ROLLBACK statement instead of COMMIT before confirming the modifications.

Explanation of DELETE FROM Statement