MySQL – Delete Query
Table of content
- MySQL DELETE Statement
- Deleting Data from a MySQL Table
- Delete Query in MySQL Using a Client Program
”;
MySQL DELETE Statement
If we want to delete a record from any MySQL table, then we can use the SQL command DELETE FROM. This statement is a part of Data Manipulation Language in SQL as it interacts with the data in a MySQL table rather than the structure.
DELETE statement can be used to delete multiple rows of a single table and records across multiple tables. However, in order to filter the records to be deleted, we can use the WHERE clause along with the DELETE statement.
We can use this command at the mysql> prompt as well as in any script like PHP, Node.js, Java, and Python.
Syntax
Following is the basic SQL syntax of the DELETE command to delete data from a MySQL table −
DELETE FROM table_name [WHERE Clause]
-
If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
-
We can specify any condition using the WHERE clause.
-
We can delete records in a single table at a time.
The WHERE clause is very useful when you want to delete selected rows in a table.
Deleting Data from a MySQL Table
We use the MySQL DELETE query to delete records from a database table. However, we can delete single, multiple rows at once or even delete all records from a table in a single query. Let us discuss them one by one futher in this tutorial with appropriate examples.
Example
First of all, let us create a table named CUSTOMERS using the following query −
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) );
The following query inserts 7 records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 );
Execute the below SELECT statement to retrieve all the records present in the CUSTOMERS table −
Select * From CUSTOMERS;
Following is the CUSTOMERS table −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
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 |
Now, let us delete a single record (ID = 1) from the CUSTOMERS table using the DELETE statement as follows −
DELETE FROM CUSTOMERS WHERE ID = 1;
Output
On executing the given query, the output is displayed as follows −
Query OK, 1 row affected (0.00 sec)
Verification
Execute the following query to verify whether the above record have been deleted or not −
Select * From CUSTOMERS;
As we can see in the output, the row with ID=1 has been deleted −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
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 |
Deleting Multiple Rows
We can also delete multiple rows using the DELETE statement. For this, we just have to specify multiple conditions in the WHERE clause that are satisfied by all the records that are supposed to be deleted.
Example
Here, we are deleting records from previously created CUSTOMERS table whose ID is 2 and 3 −
DELETE FROM CUSTOMERS WHERE ID = 2 OR ID = 3;
Output
The output for the program above is produced as given below −
Query OK, 2 rows affected (0.01 sec)
Verification
Execute the following query to verify whether the above records have been deleted or not −
Select * From CUSTOMERS;
As we can see in the output, the row with ID values 2 and 3 are deleted −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Deleting All Rows
If we want to delete all records from a MySQL table, simply execute the DELETE statement without using the WHERE clause. Following is the syntax −
DELETE FROM table_name;
Example
The following query will delete all records from the CUSTOMERS table −
DELETE FROM CUSTOMERS;
Output
On executing the given program, the output is displayed as follows −
Query OK, 4 rows affected (0.01 sec)
Verification
Execute the following query to verify whether all the records from CUSTOMERS table have been deleted or not −
Select * From CUSTOMERS;
As we can see the output below, empty set has been returned i.e all the records have been deleted.
Empty set (0.00 sec)
Delete Query in MySQL Using a Client Program
Besides deleting records of a database table with a MySQL query, we can also use a client program to perform the DELETE operation.
Syntax
Following are the syntaxes of this operation in various programming languages −
To delete data in a database table through PHP program, we need to execute the DELETE statement using the mysqli function query() as follows −
$sql="DELETE FROM table_name [WHERE Clause]"; $mysqli->query($sql);
To delete data in a database table through Node.js program, we need to execute the DELETE statement using the query() function of the mysql2 library as follows −
sql = "DELETE FROM table_name [WHERE Clause]"; VALUES (value1, value2, value3,...valueN)" con.query(sql);
To delete data in a database table through Java program, we need to execute the DELETE statement using the JDBC function executeUpdate() as follows −
String sql="DELETE FROM table_name [WHERE Clause]"; statement.executeUpdate(sql);
To delete data in a database table through Python program, we need to execute the DELETE statement using the execute() function of the MySQL Connector/Python as follows −
delete_query = "DELETE FROM table_name [WHERE Clause]" cursorObj.execute(delete_query);
Example
Following are the programs −
$dbhost = ''localhost''; $dbuser = ''root''; $dbpass = ''root@123''; $dbname = ''TUTORIALS''; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if($mysqli->connect_errno ) { printf("Connect failed: %s<br />", $mysqli->connect_error); exit(); } printf(''Connected successfully.<br />''); if ($mysqli->query(''DELETE FROM tutorials_tbl where tutorial_id = 4'')) { printf("Table tutorials_tbl record deleted successfully.<br />"); } if ($mysqli->errno) { printf("Could not delete record from table: %s<br />", $mysqli->error); } $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"; $result = $mysqli->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", $row["tutorial_id"], $row["tutorial_title"], $row["tutorial_author"], $row["submission_date"]); } } else { printf(''No record found.<br />''); } mysqli_free_result($result); $mysqli->close();
Output
The output obtained is as follows −
Connected successfully. Table tutorials_tbl record deleted successfully. Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021 Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021 Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021 Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); //Selecting a Database sql = "USE TUTORIALS" con.query(sql); //Updating a single record in the table sql = "DELETE FROM tutorials_tbl WHERE tutorial_id = 5;" con.query(sql); //Selecting records from table sql = "SELECT * FROM tutorials_tbl" con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); });
Output
The output produced is as follows −
Connected! [ { tutorial_id: 1, tutorial_title: ''Learn PHP'', tutorial_author: ''John Paul'', submission_date: 2023-07-25T18:30:00.000Z }, { tutorial_id: 2, tutorial_title: ''Learn MySQL'', tutorial_author: ''Abdul S'', submission_date: 2021-03-27T18:30:00.000Z }, { tutorial_id: 3, tutorial_title: ''Learning Java'', tutorial_author: ''Sanjay'', submission_date: 2007-05-05T18:30:00.000Z }, { tutorial_id: 4, tutorial_title: ''Python Tutorial'', tutorial_author: ''Sasha Lee'', submission_date: 2016-09-03T18:30:00.000Z } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class DeleteQuery { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "DELETE FROM tutorials_tbl WHERE tutorial_id = 6"; st.executeUpdate(sql); System.out.println("Delete query executed successfully..!"); String sql1 = "SELECT * FROM tutorials_tbl"; rs = st.executeQuery(sql1); System.out.println("Table records: "); while(rs.next()) { String tutorial_id = rs.getString("tutorial_id"); String tutorial_title = rs.getString("tutorial_title"); String tutorial_author = rs.getString("tutorial_author"); String submission_date = rs.getString("submission_date"); System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " + tutorial_author + ", Submission_date: " + submission_date); } }catch(Exception e) { e.printStackTrace(); } } }
Output
The output obtained is as shown below −
Delete query executed successfully..! Table records: Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08 Id: 2, Title: Angular Java, Author: Abdul S, Submission_date: 2023-08-08 Id: 3, Title: Learning Java, Author: Sanjay, Submission_date: 2007-05-06 Id: 4, Title: Python Tutorial, Author: Sasha Lee, Submission_date: 2016-09-04 Id: 5, Title: Hadoop Tutorial, Author: Chris Welsh, Submission_date: 2023-08-08
import mysql.connector import datetime #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) #Creating a cursor object cursorObj = connection.cursor() delete_query = "DELETE FROM tutorials_tbl WHERE tutorial_id = 6" cursorObj.execute(delete_query) connection.commit() print("Row deleted successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
Row deleted successfully.
”;