SQL - DELETE Query

The SQL DELETE Statement

The SQL DELETE Statement is used to delete the records from an existing table. In order to filter the records to be deleted (or, delete particular records), we need to use the WHERE clause along with the DELETE statement.

If you execute DELETE statement without a WHERE clause, it will delete all the records from the table.

Using the DELETE statement, we can delete one or more rows of a single table and records across multiple tables.

Syntax

The basic syntax of the SQL DELETE Query with the WHERE clause is as follows −

 DELETE FROM table_name WHERE [condition]; 

You can combine N number of conditions using AND or OR operators.

Example

Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below −

 CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); 

Now, insert values into this table using the INSERT statement as follows −

 INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ), (2, 'Khilan', 25, 'Delhi', 1500.00 ), (3, 'Kaushik', 23, 'Kota', 2000.00 ), (4, 'Chaitali', 25, 'Mumbai', 6500.00 ), (5, 'Hardik', 27, 'Bhopal', 8500.00 ), (6, 'Komal', 22, 'Hyderabad', 4500.00 ), (7, 'Muffy', 24, 'Indore', 10000.00 ); 

The table will be created as −

The following query deletes the record of a customer, whose ID is 6.

 DELETE FROM CUSTOMERS WHERE ID = 6; 

Output

The output will be displayed as −

 Query OK, 1 row affected (0.10 sec) 

Verification

To verify whether the record(s) have been deleted from the table, we need to retrieve the modified table using the SELECT query, as shown below −

 SELECT * FROM CUSTOMERS; 

Now, the CUSTOMERS table would have the following records −

Deleting Multiple Rows

To delete multiple rows from a table, we need to specify the required condition(s), that is satisfied by all the rows to be deleted, using the WHERE clause. Let us look at an example −

Example

From the same Customers table, let us try to delete the records of customers who are over 25 years of age.

 DELETE FROM CUSTOMERS WHERE AGE > 25; 

Output

The output will be displayed as −

 Query OK, 2 rows affected (0.06 sec) 

Verification

To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below −

 SELECT * FROM CUSTOMERS; 

The query above will produce the following table −

Deleting All The Records From a Table

If we want to DELETE all the records from an existing table (truncate it) using the DELETE query, we simply need to run it without using the WHERE clause.

Example

Following SQL query removes all the records from the CUSTOMERS table −

 DELETE FROM CUSTOMERS; 

Output

The output will be displayed as −

 Query OK, 4 rows affected (0.13 sec) 

Verification

To verify whether all the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

 SELECT * FROM CUSTOMERS; 

Now, the CUSTOMERS table would not have any record and will show the following output −

 Empty set (0.00 sec) 

Delete Records in Multiple Tables

SQL allows us to delete the records from multiple tables using the DELETE query. In here, we will use the JOIN clause to combine data from multiple tables (based on a common column).

Example

Let us create another table with name ORDERS which contains the details of the orders made by the customers.

 CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); 

Using the INSERT statement, insert values into this table as follows

 INSERT INTO ORDERS VALUES (102, '2009-10-08 00:00:00', 3, 3000.00), (100, '2009-10-08 00:00:00', 3, 1500.00), (101, '2009-11-20 00:00:00', 2, 1560.00), (103, '2008-05-20 00:00:00', 4, 2060.00); 

The table created is as shown below −

Following SQL query deletes the records of the customers (from the tables CUSTOMERS and ORDERS) who earn more than 2000 and have placed orders −

 DELETE CUSTOMERS, ORDERS FROM CUSTOMERS INNER JOIN ORDERS ON ORDERS.CUSTOMER_ID = CUSTOMERS.ID WHERE CUSTOMERS.SALARY > 2000; 

Output

The output will be displayed as −

 Query OK, 2 rows affected (0.01 sec) 

Verification

To verify whether the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

 SELECT * FROM CUSTOMERS; 

The CUSTOMERS table would not have the record(s) where salary is greater than 2000 and the ID matches with the CUSTOMER_ID column in the ORDERS table.

Similarly, if you verify the ORDERS table as shown below −

 SELECT * FROM ORDERS; 

Since salary is greater than 2000 and the CUSTOMER_ID matches with the ID value in the CUSTOMERS table, the last record (OID 103) of the ORDERS table will be deleted −