The SQL DELETE
statement is used to remove existing records from a table. This statement allows you to delete specific rows based on a condition or all rows in a table.
DELETE FROM table_name
WHERE condition;
The DELETE
statement has the following components:
table_name
: The name of the table from which to delete the data.condition
: The condition for selecting rows to delete. If omitted, all rows in the table will be deleted.Let's look at some examples of SQL DELETE
statement queries:
DELETE FROM employees
WHERE id = 1;
This query deletes the row from the employees
table where the id
is 1. The result will be that the row with id
1 is removed from the table.
DELETE FROM employees
WHERE last_name = 'Smith';
This query deletes all rows from the employees
table where the last_name
is 'Smith'. The result will be that all rows with the last name 'Smith' are removed from the table.
DELETE FROM employees;
This query deletes all rows from the employees
table. The result will be that the employees
table is emptied of all its data.
Let's go through a complete example that includes creating a table, inserting data, deleting data, and querying the table.
This step involves creating a new table named employees
to store employee data.
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
In this example, we create a table named employees
with columns for id
, first_name
, last_name
, and email
.
This step involves inserting some sample data into the employees
table.
INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');
INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane.smith@example.com');
INSERT INTO employees (first_name, last_name, email)
VALUES ('Jim', 'Brown', 'jim.brown@example.com');
Here, we insert data into the employees
table.
This step involves deleting some data from the employees
table.
DELETE FROM employees
WHERE id = 2;
Here, we delete the row with id
2 from the employees
table.
This step involves selecting the data from the employees
table to view the remaining records.
SELECT *
FROM employees;
This query retrieves all the rows from the employees
table. The result will be:
id first_name last_name email
--- ----------- ---------- ------------------------
1 John Doe john.doe@example.com
3 Jim Brown jim.brown@example.com
The SQL DELETE
statement is a powerful tool for removing existing records from a table. Understanding how to use the DELETE
statement and its syntax is essential for effective data management and manipulation in SQL databases.