Syntax error Select from another column if selected value is '0' in MySQL?

Select from another column if selected value is '0' in MySQL?

For this, use IF() in MySQL. The syntax is as follows −

select IF(yourColumnName1=0,yourColumnName2,yourColumnName1) as anyAliasName
from yourTableName;

Let us create a table −

mysql> create table demo30
−> (
−> id int not null auto_increment primary key,
−> value int,
−> original_value int
−> )
−> ;
Query OK, 0 rows affected (1.87 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo30(value,original_value) values(50,10);
Query OK, 1 row affected (0.10 sec)

mysql> insert into demo30(value,original_value) values(1000,0);
Query OK, 1 row affected (0.13 sec)

mysql> insert into demo30(value,original_value) values(0,40);
Query OK, 1 row affected (0.15 sec)

mysql> insert into demo30(value,original_value) values(30,0);
Query OK, 1 row affected (0.09 sec)

Display records from the table using select statement −

mysql> select *from demo30;

This will produce the following output −

+----+-------+----------------+
| id | value | original_value |
+----+-------+----------------+
| 1  | 50    | 10             |
| 2  | 1000  | 0              |
| 3  | 0     | 40             |
| 4  | 30    | 0              |
+----+-------+----------------+
4 rows in set (0.00 sec)

Following is the query to select from another column if selected value is '0' in MySQL −

mysql> select id, if(original_value= 0,value,original_value) as Result
−> from demo30;

This will produce the following output −

+----+--------+
| id | Result |
+----+--------+
|  1 | 10     |
|  2 | 1000   |
|  3 | 40     |
|  4 | 30     |
+----+--------+
4 rows in set (0.00 sec)
Updated on: 2020-11-19T12:27:30+05:30

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements