How to create conditions in a MySQL table with multiple columns?

MySQLMySQLi Database

For conditions, use IF(). Following is the syntax −

IF(yourCondition, trueStatement,falseStatement);

Let us first create a table −

mysql> create table DemoTable612 (Number1 int,Number2 int,Score int);
Query OK, 0 rows affected (0.47 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable612 values(10,20,1000);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable612 values(30,40,500);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable612 values(50,70,1200);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable612 values(100,120,400);
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable612;

This will produce the following output −

+---------+---------+-------+
| Number1 | Number2 | Score |
+---------+---------+-------+
|      10 |      20 | 1000  |
|      30 |      40 | 500   |
|      50 |      70 | 1200  |
|     100 |     120 | 400   |
+---------+---------+-------+
4 rows in set (0.00 sec)

Here is the query to create conditions in MySQL −

mysql> select *,If(Score > 500, Number1*Number2,Number1+Number2) AS Result from DemoTable612;

This will produce the following output −

+---------+---------+-------+--------+
| Number1 | Number2 | Score | Result |
+---------+---------+-------+--------+
|      10 |      20 |  1000 |    200 |
|      30 |      40 |   500 |     70 |
|      50 |      70 |  1200 |   3500 |
|     100 |     120 |   400 |    220 |
+---------+---------+-------+--------+
4 rows in set (0.00 sec)
raja
Published on 22-Aug-2019 13:48:29
Advertisements