MySQL UPDATE using IF condition

The syntax is as follows to perform UPDATE using IF condition in MySQL −

update yourTableName set yourColumnName =if(yourColumnName =yourOldValue,yourNewValue,yourColumnName);

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table updateIfConditionDemo
   -> (
   -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> UserName varchar(20),
   -> UserAge int
   -> );
Query OK, 0 rows affected (4 min 0.59 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into updateIfConditionDemo(UserName,UserAge) values('Larry',23);
Query OK, 1 row affected (0.11 sec)
mysql> insert into updateIfConditionDemo(UserName,UserAge) values('Mike',21);
Query OK, 1 row affected (0.20 sec)
mysql> insert into updateIfConditionDemo(UserName,UserAge) values('Sam',23);
Query OK, 1 row affected (0.15 sec)
mysql> insert into updateIfConditionDemo(UserName,UserAge) values('David',23);
Query OK, 1 row affected (0.14 sec)
mysql> insert into updateIfConditionDemo(UserName,UserAge) values('Maxwell',23);
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from updateIfConditionDemo;

Here is the output −

+--------+----------+---------+
| UserId | UserName | UserAge |
+--------+----------+---------+
| 1      | Larry    | 23      |
| 2      | Mike     | 21      |
| 3      | Sam      | 23      |
| 4      | David    | 23      |
| 5      | Maxwell  | 23      |
+--------+----------+---------+
5 rows in set (0.00 sec)

Here is the query to update using IF condition −

mysql> update updateIfConditionDemo set UserAge =if(UserAge =23,26,UserAge);
Query OK, 4 rows affected (0.20 sec)
Rows matched: 5 Changed: 4 Warnings: 0

Let us check the table records once again. The UserAge has been updated from 23 to 26 −

mysql> select *from updateIfConditionDemo;

Here is the output −

+--------+----------+---------+
| UserId | UserName | UserAge |
+--------+----------+---------+
| 1      | Larry    | 26      |
| 2      | Mike     | 21      |
| 3      | Sam      | 26      |
| 4      | David    | 26      |
| 5      | Maxwell  | 26      |
+--------+----------+---------+
5 rows in set (0.00 sec)