Syntax error Select last record and update it in MySQL?

Select last record and update it in MySQL?

For this, you can use ORDER BY DESC LIMIT. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(Name) values('John');
Query OK, 1 row affected (0.12 sec)

mysql> insert into DemoTable(Name) values('Sam');
Query OK, 1 row affected (0.19 sec)

mysql> insert into DemoTable(Name) values('Carol');
Query OK, 1 row affected (0.34 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

+----+-------+
| Id | Name  |
+----+-------+
|  1 | John  |
|  2 | Sam   |
|  3 | Carol |
+----+-------+
3 rows in set (0.00 sec)

Following is the query to select last record and update it −

mysql> update DemoTable
   -> set Name='Robert'
   -> order by Id DESC limit 1;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1  Changed: 1 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output displaying we have successfully updated the last record −

Output

+----+--------+
| Id | Name   |
+----+--------+
|  1 | John   |
|  2 | Sam    |
|  3 | Robert |
+----+--------+
3 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:26+05:30

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements