Multiple IF statements on MYSQL

Question

I'm trying to Display somes values in my database result, I am using this code but I can not succeed:

SELECT 
   item_code, 
   IF(category_code = 'HERR1', 'NO', 1) OR (category_code = 'COLN5', 'NO', 2) AS category_code, 
   item_name, 
   item_quantity 
FROM qa_items

EDIT : I Want to display for example:

If category_code = 'HERR1'
 Display = 1
else if category_code = 'COLN5'
 Display = 2
End If

If anyone has any idea, would greatly appreciate it

Well, a select statement does not result in any change in underlying data. You need to use an UPDATE statement instead. — Chetter Hummin, Mar 10 '12 at 17:20
What errors are you getting? Can you post the expected and actual outputs? — Chetter Hummin, Mar 10 '12 at 17:25
Can you explain what you are trying to achieve? Do you want to display 1 or 2 if `category_code` is neither 'HERR1' nor 'COLN5' ? — a1ex07, Mar 10 '12 at 17:26

score 46 · Accepted Answer · answered Mar 10 '12 at 17:32

I'd rather use CASE :

SELECT item_code, 
CASE category_code 
WHEN 'HERR1' THEN 1
WHEN 'COLN5' THEN 2
ELSE 'NO'
END as category_code, item_name, item_quantity 
FROM qa_items

But IF will also work : IF(category_code='HERR1',1, IF(category_code='COLN5',2,'NO'))

Mark Willis · Answer 2 · 2012-07-24T10:00:21.190

14

You need to nest the if statements

SELECT item_code, IF(category_code = 'HERR1', 'NO', IF(category_code = 'COLN5', 1, 2)) AS category_code, item_name, item_quantity FROM qa_items

Then the first if will fail and the nested if will evaluate

edited Jul 24 '12 at 10:00

answered Mar 10 '12 at 17:30

Mark Willis

1,711
1
14
23

score 6 · Answer 3 · answered Mar 10 '12 at 17:32

6

Is this what you were after?

SELECT
  item_code,
  CASE category_code
    WHEN 'HERR1' THEN 1
    WHEN 'COLN5' THEN 2
    ELSE 'NO'
  END AS category_code,
  item_name,
  item_quantity
FROM qa_items

answered Mar 10 '12 at 17:32

user1191247

10,808
2
22
32

score 1 · Answer 4 · answered Sep 30 '19 at 06:55

You can try this. Use IF in select query and update the table you want ;)

create table student(marks int,grade char);

insert into student values(200,null),(120,null), (130,null);

UPDATE student a INNER JOIN (select s.marks, IF(s.marks>=200,'A',IF(s.marks>=130,'B','P')) AS Grade from student s) b on a.marks= b.marks SET a.Grade = b.Grade;

score 1 · Answer 5 · answered Mar 10 '12 at 17:35

1

Try the following

SELECT item_code, CASE category_code WHEN 'HERR1' THEN 1 WHEN 'COLN5' THEN 0 ELSE 'NONE' END AS category_code, item_name, item_quantity FROM qa_items

answered Mar 10 '12 at 17:35

Chetter Hummin

6,687
8
32
44

score 0 · Answer 6 · edited Jun 08 '22 at 01:19

0

SELECT MOBILE,
       CASE (SUBSTRING(mobile, LENGTH(MOBILE), 1)) WHEN ',' 
           THEN SUBSTRING(mobile, 1, LENGTH(MOBILE) - 1)
           ELSE SUBSTRING(mobile, 1, LENGTH(MOBILE)) 
       END AS newmobile
FROM (SELECT CONCAT(IFNULL(`mob1`, ''), IF(`mob1` IS NULL, '', ','),
                    IFNULL(`mob2`, ''), IF(`mob2` IS NULL, '', ','),
                    IFNULL(`mob3`, ''), IF(`mob3` IS NULL, '', ','),
                    IFNULL(`mob4`, ''), IF(`mob4` IS NULL, '', ','),
                    IFNULL(`mob5`, ''), IF(`mob5` IS NULL, '', ','),
                    IFNULL(`mob6`, ''))
                 AS mobile
      FROM `temp_consignordata`) AS T

edited Jun 08 '22 at 01:19

Rulisp

1,586
1
18
30

answered Jun 03 '22 at 08:04

PAWAN DUBEY

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 03 '22 at 10:42

score 0 · Answer 7 · answered Jul 16 '22 at 09:09

SELECT item_code, 
   -- First if
   IF(category_code = 'HERR1', 1, 
   -- second else IF
    IF(category_code = 'COLN5', 2,
   -- last else
   'NO') 
 AS category_code, 
 item_name, 
 item_quantity 
FROM qa_items;

Explanation

first if evalutes for value 'HERR1' and if found assigns 1
Second (else ) IF evalues for Value 'COLN5' and if found assigns 2
Last (else) default case assigns 'NO'
to category_code

Multiple IF statements on MYSQL

7 Answers7

Explanation

Linked