MySQL UPDATE query where id is highest AND field is equal to variable?

The syntax is as follows

update yourTableName
set yourColumnName1=yourValue where yourColumnName2=yourValue order by yourIdColumnName DESC LIMIT 1;

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

mysql> create table UpdateWithHighestDemo
   -> (
   -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> UserStatus tinyint,
   -> UserRank int
   -> );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into UpdateWithHighestDemo(UserStatus,UserRank) values(1,78);
Query OK, 1 row affected (0.12 sec)
mysql> insert into UpdateWithHighestDemo(UserStatus,UserRank) values(0,118);
Query OK, 1 row affected (0.18 sec)
mysql> insert into UpdateWithHighestDemo(UserStatus,UserRank) values(1,223);
Query OK, 1 row affected (0.62 sec)
mysql> insert into UpdateWithHighestDemo(UserStatus,UserRank) values(1,225);
Query OK, 1 row affected (0.12 sec)
mysql> insert into UpdateWithHighestDemo(UserStatus,UserRank) values(0,227);
Query OK, 1 row affected (0.14 sec)
mysql> insert into UpdateWithHighestDemo(UserStatus,UserRank) values(0,230);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from UpdateWithHighestDemo;

The following is the output

+--------+------------+----------+
| UserId | UserStatus | UserRank |
+--------+------------+----------+
| 1      | 1          | 78       |
| 2      | 0          | 118      |
| 3      | 1          | 223      |
| 4      | 1          | 225      |
| 5      | 0          | 227      |
| 6      | 0          | 230      |
+--------+------------+----------+
6 rows in set (0.00 sec)

Here is the query to update column

mysql> update UpdateWithHighestDemo
-> set UserStatus=1 where UserRank=230 order by UserId DESC LIMIT 1;
Query OK, 1 row affected (0.19 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Let us check and display records from the table using select statement.

The query is as follows

mysql> select *from UpdateWithHighestDemo;

The following is the output

+--------+------------+----------+
| UserId | UserStatus | UserRank |
+--------+------------+----------+
| 1      | 1          | 78       |
| 2      | 0          | 118      |
| 3      | 1          | 223      |
| 4      | 1          | 225      |
| 5      | 0          | 227      |
| 6      | 1          | 230      |
+--------+------------+----------+
6 rows in set (0.00 sec)

Now if you want to update with highest id then ORDER BY clause is useful. In the above sample output the highest ‘UserId’=6 and UserStatus is 1.

Let us update UserStatus to 0.

The query is as follows

mysql> update UpdateWithHighestDemo
   -> set UserStatus=0 order by UserId DESC LIMIT 1;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Check the records from the table using select statement.

The query is as follows

mysql> select *from UpdateWithHighestDemo;
+--------+------------+----------+
| UserId | UserStatus | UserRank |
+--------+------------+----------+
| 1      | 1          | 78       |
| 2      | 0          | 118      |
| 3      | 1          | 223      |
| 4      | 1          | 225      |
| 5      | 0          | 227      |
| 6      | 0          | 230      |
+--------+------------+----------+
6 rows in set (0.00 sec)