DELETE query in SQL
DELETE is a fundamental SQL statement used to remove existing records in a database table. SQL DELETE query allows you to delete one or more rows based on specified conditions. Whenever you need to delete one or more records in a database table you should use SQL delete query. This article provides a detailed overview of how to effectively use the DELETE statement in SQL.
The primary purpose of the DELETE statement is to remove records that already exists in a table. It is commonly used when you need to delete one or more records based on certain condition.
Syntax
DELETE FROM table_name WHERE condition;
- DELETE statement is used to remove existing records in a database table.
- DELETE query allows you to remove one or more records based on specified conditions.
- It's recommended to always use a meaningful WHERE clause with DELETE statement to avoid unintentional deletion of all records in a table.
- Always execute DELETE with caution, especially in production environments.
- Before executing DELETE query on production environment it is advised to test them in a development or staging environment to verify their correctness.
- Before executing DELETE query, especially affecting large portions of data, it is recommended to take a latest backup of the database to mitigate risks of data loss.
- When deleting critical data, consider using transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK) to ensure Integrity, atomicity and consistency.
- DELETE statement supports complex conditions and expressions, enabling dynamic deletes based on various criteria.
- Be mindful when executing delete operations as they cannot be reversed once confirmed.
- Deleting large amounts of data can significantly impact database performance, especially if DELETE query is not optimized. Ensure indexes are properly maintained and lookup DELETE query execution Plan to optimize DELETE operations, especially for tables that contain millions of records.
Be careful when deleting records from a table. Always include a WHERE clause in your DELETE statement to specify which record(s) to delete. Omitting the WHERE clause will result in deleting all records in the table.
Demo Database
CREATE TABLE Users (
Id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Mobile VARCHAR(15) UNIQUE NOT NULL,
DOB DATE,
Weight DECIMAL(5, 2)
);
CREATE TABLE UserAddress (
Id INT AUTO_INCREMENT PRIMARY KEY,
UserId INT NOT NULL,
AddressType ENUM('Permanent', 'Temporary', 'Other') NOT NULL,
Address VARCHAR(255) NOT NULL,
Country VARCHAR(50),
City VARCHAR(50),
PostalCode VARCHAR(10)
);Deleting Specific Rows
You can use DELETE statement to remove specific rows. For deleting specific rows define a precise WHERE condition that meets your criteria.
SQL query to remove records of Delhi city.
DELETE FROM UserAddress WHERE City="Delhi";
Deleting Single Row
To delete a single row, use the primary key or a unique column value in the WHERE clause for exact matching, ensuring that only the intended row is deleted.
DELETE FROM Users WHERE Id=3;
Deleting With Complex Conditions
If you have a complex business logic for deleting table records use the DELETE statement along with WHERE clause, inside WHERE clause define the specific conditions that meet your business logic criteria.
DELETE FROM UserAddress WHERE Country="India" AND State ="Maharashtra" AND City="Pune" AND AddressType="Permanent";
Deleting All Rows/Records
You can delete all rows from a table without deleting table while preserving its structure, attributes, and indexes. It is advised to take a latest backup of the database before delete operations as they cannot be reversed once confirmed.
SQL query to delete all users from Users table.
DELETE FROM Users;
Key Differences Between DELETE and TRUNCATE
DML (Data Manipulation Language). Can be rolled back.
DDL (Data Definition Language). Cannot be rolled back.
Delete a Table
if you want to delete the table completely use DROP TABLE statement.
SQL query to delete Users table.
DROP TABLE Users;