Combine MySQL UPDATE STATEMENTS?

MySQLMySQLi Database

You can use a CASE statement for this. Let us first create a table −

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

Insert records in the table using insert command −

mysql> insert into DemoTable(Name) values('John');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable(Name) values('Carol');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(Name) values('David');
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable;

This will produce the following output −

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

Following is the query to combine MySQL UPDATE STATEMENTS −

mysql> UPDATE DemoTable set Name= case when Name= 'John' then 'John Smith'
when Name= 'Carol' then 'Carol Taylor'
when Name= 'David' then 'David Miller'
end
where Name IN('John','Carol','David');
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Let us display the updated records from the table −

mysql> select * from DemoTable;

This will produce the following output −

+----+--------------+
| Id | Name         |
+----+--------------+
| 1  | John Smith   |
| 2  | Carol Taylor |
| 3  | David Miller |
+----+--------------+
3 rows in set (0.00 sec)
raja
Published on 03-May-2019 14:05:22
Advertisements