How to delete last record (on condition) from a table in MySQL?

MySQLMySQLi Database

To delete last record (on condition) from a table, you need to use ORDER BY DESC with LIMIT 

1. The syntax is as follows:

DELETE FROM yourTableName WHERE yourColumnName1=yourValue ORDER BY yourColumnName2 DESC LIMIT 1;

The above syntax will delete last record (on condition) from a table. It sorts the column in descending order and choose the first element to delete.

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table UserLoginTable
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> UserId int,
   -> UserLoginDateTime datetime,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.94 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(2,'2019-01-27 13:47:20');
Query OK, 1 row affected (0.19 sec)
mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(1,'2018-11-28 12:30:12');
Query OK, 1 row affected (0.16 sec)
mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(2,'2019-01-26 11:30:30');
Query OK, 1 row affected (0.20 sec)
mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(1,'2015-03-11 15:23:55');
Query OK, 1 row affected (0.21 sec)
mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(2,'2019-03-21 16:01:56');
Query OK, 1 row affected (0.23 sec)

Now you can display all records from the table using select statement. The query is as follows:

mysql> select *from UserLoginTable;

The following is the output:

+----+--------+---------------------+
| Id | UserId | UserLoginDateTime   |
+----+--------+---------------------+
|  1 |      2 | 2019-01-27 13:47:20 |
|  2 |      1 | 2018-11-28 12:30:12 |
|  3 |      2 | 2019-01-26 11:30:30 |
|  4 |      1 | 2015-03-11 15:23:55 |
|  5 |      2 | 2019-03-21 16:01:56 |
+----+--------+---------------------+
5 rows in set (0.00 sec)

Here is the query to delete last record (on condition) from a table:

mysql> delete from UserLoginTable where UserId=2 ORDER BY UserLoginDateTime DESC LIMIT 1;
Query OK, 1 row affected (0.16 sec)

Check the table record once again using select statement. The query is as follows:

mysql> select *from UserLoginTable;

The following is the output:

+----+--------+---------------------+
| Id | UserId | UserLoginDateTime   |
+----+--------+---------------------+
|  1 |      2 | 2019-01-27 13:47:20 |
|  2 |      1 | 2018-11-28 12:30:12 |
|  3 |      2 | 2019-01-26 11:30:30 |
|  4 |      1 | 2015-03-11 15:23:55 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

Last record is now deleted from a table with UserId 2.

raja
Published on 27-Feb-2019 05:57:04
Advertisements