How to delete a single value from a MySQL table with duplicate records?

MySQLMySQLi Database

Use DELETE to delete records, but if you have multiple duplicate records, then limit the deletion using MySQL LIMIT as in the below syntax −

Syntax

delete from yourTableName where yourColumnName=yourValue limit 1;

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Amount int
   -> );
Query OK, 0 rows affected (1.38 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(50);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values(100);
Query OK, 1 row affected (0.36 sec)
mysql> insert into DemoTable values(50);
Query OK, 1 row affected (0.59 sec)
mysql> insert into DemoTable values(70);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values(90);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(50);
Query OK, 1 row affected (0.23 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------+
| Amount |
+--------+
|     50 |
|    100 |
|     50 |
|     70 |
|     90 |
|     50 |
+--------+
6 rows in set (0.00 sec)

Here is the query to delete a single value from a table −

mysql> delete from DemoTable where Amount=50 limit 1;
Query OK, 1 row affected (0.39 sec)

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output −

+--------+
| Amount |
+--------+
|    100 |
|     50 |
|     70 |
|     90 |
|     50 |
+--------+
5 rows in set (0.00 sec)
raja
Published on 13-Dec-2019 06:20:20
Advertisements