Update multiple values in a table with MySQL IF Statement
Let us first create a table −
mysql> create table DemoTable716 (
Id varchar(100),
Value1 int,
Value2 int,
Value3 int
);
Query OK, 0 rows affected (0.65 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable716 values('100',45,86,79);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable716 values('101',67,67,99);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable716 values('102',77,57,98);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable716 values('103',45,67,92);
Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −
mysql> select *from DemoTable716;
This will produce the following output -
+------+--------+--------+--------+
| Id | Value1 | Value2 | Value3 |
+------+--------+--------+--------+
| 100 | 45 | 86 | 79 |
| 101 | 67 | 67 | 99 |
| 102 | 77 | 57 | 98 |
| 103 | 45 | 67 | 92 |
+------+--------+--------+--------+
4 rows in set (0.00 sec)
Following is the query to update multiple values in a table −
mysql> update DemoTable716
set Value3=if(Value1=67 OR Value2=67,67,NULL) where Id='101';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Let us check the table records once again −
mysql> select *from DemoTable716;
This will produce the following output -
+------+--------+--------+--------+
| Id | Value1 | Value2 | Value3 |
+------+--------+--------+--------+
| 100 | 45 | 86 | 79 |
| 101 | 67 | 67 | 67 |
| 102 | 77 | 57 | 98 |
| 103 | 45 | 67 | 92 |
+------+--------+--------+--------+
4 rows in set (0.00 sec)
Published on 22-Aug-2019 10:44:13
