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

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)
Updated on: 2019-07-30T22:30:25+05:30

575 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements