How to use a select statement while updating in MySQL?

For this, use sub query along with WHERE clause while using the MySQL UPDATE command. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Id int,
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.82 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(100,'Chris');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values(250,'David');
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable values(150,'Mike');
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+------+-------+
| Id   | Name  |
+------+-------+
| 100  | Chris || 250 | David |
| 150  | Mike  |
+------+-------+
3 rows in set (0.00 sec)

Here is the query to use a select statement while updating −

mysql> update DemoTable
   -> set Name='Robert'
   -> where Id in
   -> (
   -> select *from ( select max(Id) from DemoTable ) tbl1
   -> );
Query OK, 1 row affected (0.27 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 −

+------+--------+
| Id   | Name   |
+------+--------+
| 100  | Chris  |
| 250  | Robert |
| 150  | Mike   |
+------+--------+
3 rows in set (0.00 sec)

AmitDiwan

Updated on 17-Dec-2019 07:58:32

• Related Questions & Answers
• How to use NULL in MySQL SELECT statement?
• How to use custom variable while updating a MongoDB document?
• How to use the CAST function in a MySQL SELECT statement?
• How to use MySQL CASE statement while using UPDATE Query?
• Can we use SELECT NULL statement in a MySQL query?
• MySQL case statement inside a select statement?
• How to use prepared statement for select query in Java with MySQL?
• How can I use MySQL IF() function within SELECT statement?
• How can we use MySQL SELECT statement to count number of rows in a table?
• How can we use SET statement to assign a SELECT result to a MySQL user variable?
• Add a percentage (%) sign at the end to each value while using MySQL SELECT statement
• IF() function in a MySQL Select statement?
• How to use PowerShell Break statement with the While Loop?
• How to implement WHILE LOOP with IF STATEMENT MySQL?
• How do we use continue statement in a while loop in C#?