MySQL query to update different fields based on a condition?

Let us first create a −

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

Insert some records in the table using insert −

mysql> insert into DemoTable1436(Name) values('Chris'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1436(Name) values('David'); Query OK, 1 row affected (0.40 sec) mysql> insert into DemoTable1436(Name) values('Bob'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable1436(Name) values('David'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1436(Name) values('David'); Query OK, 1 row affected (0.31 sec)

Display all records from the table using select −

mysql> select * from DemoTable1436;

This will produce the following output −

+----+-------+ | Id | Name  | +----+-------+ |  1 | Chris | |  2 | David | |  3 | Bob   | |  4 | David | |  5 | David | +----+-------+ 5 rows in set (0.00 sec)

Following is the query to update different fields based on a condition −

mysql> update DemoTable1436    -> set Name=IF(Name='David','Carol Taylor',Name)    -> where Id=2; Query OK, 1 row affected (0.31 sec) Rows matched: 1  Changed: 1 Warnings: 0

Let us check the table records once again −

mysql> select * from DemoTable1436;

This will produce the following output −

+----+--------------+ | Id | Name         | +----+--------------+ |  1 | Chris        | |  2 | Carol Taylor | |  3 | Bob          | |  4 | David        | |  5 | David        | +----+--------------+ 5 rows in set (0.00 sec)