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

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

Previous Page Print Page