SQL Delete Statement

In this lesson, you will learn how to delete records from a database table using SQL.

Delete Statement In SQL

The DELETE statement is used to delete existing records from a table as follows.

DELETE FROM table_name
WHERE condition;

Preparing A Database

Since you will learn how to delete records from a table, you must have a database table with some records to practice deleting them. Execute the following query to create the database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;

-- Create a new table called "users" inside the selected database
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    country VARCHAR(100)
);

-- Insert 10 records in the "users" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO users VALUES (null, "Emily", "United Kigndom");
INSERT INTO users VALUES (null, "Omar", "Jordan");
INSERT INTO users VALUES (null, "Olivia", "France");
INSERT INTO users VALUES (null, "William", "Norway");
INSERT INTO users VALUES (null, "Sophia", "Hawai");
INSERT INTO users VALUES (null, "Daniel", "Germany");
INSERT INTO users VALUES (null, "Ava", "Japan");
INSERT INTO users VALUES (null, "James", "Australia");
INSERT INTO users VALUES (null, "Mia", "France");
INSERT INTO users VALUES (null, "Ethan", "Russia");
    

Delete A Record Example

The following query deletes the user who has an id equal to 1.

DELETE FROM users
WHERE id = 1;
    

If you view the table now, you will notice that the user with an id equal to 1 does not exist anymore as follows.

Disable Safe Mode In MySQL Workbench

Usually, deleting multiple or all records at once is disabled by database servers and it's disabled by default in MySQL.

Now, if you try to delete multiple or all records in MySQL you may get this error message:

To disable safe mode in MySQL Workbench navigate to Edit > Click on Preferences > Click on SQL Editor tab, check the Safe Updates option, Click Ok and reconnect.

Delete Multiple Records Example

The following query deletes all users who are from France.

DELETE FROM users
WHERE country = 'France';
    

If you view the table now, you will notice that all users from France do not exist anymore as follows.

Delete All Records Example

The following query deletes all users.

DELETE FROM users;
    

If you view the table now, you will notice that the table is empty as follows.

Delete Records Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:

• id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
• first_name VARCHAR(50)
• last_name VARCHAR(50)
• phone VARCHAR(20)

3- Write a query that inserts 5 rows in the "customers" table.

4- Write a query that deletes a user with an id equal to 3.

5- Write a query that deletes a user based on his phone number.

6- Write a query that selects all records from the "customers" table.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS customers (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20)
);

INSERT INTO customers (first_name, last_name, phone) VALUES
('Jack', 'Reed', '10203040'),
('Emma', 'Clark', '55512388'),
('Luke', 'Lee', '32452012'),
('Jim', 'Wilson', '96112377'),
('Lily', 'Brooks', '36018090');

DELETE FROM customers
WHERE id = 3;

DELETE FROM customers
WHERE phone = '36018090';

SELECT * FROM customers;