CODEWITHSUNDEEP
mysqlsql
Zabs
Zabs

Reputation: 14142

Set a column value if it is NULL (MySQL)

I am trying to modify this query to set the value for the 'reason' column if it is empty then set the value to 'CUSTOM' (the third column across in the example below)

SELECT
    `t`.`myuser_id` AS `t0_c1`,
    IF (positive.reason IS NOT NULL,
        positive.reason,
        deduction.reason
    ) AS reason,
    `positive`.`achievement_id` AS `t1_c0`,
    `positive`.`reason` AS `t1_c2`,
    `deduction`.`reason` AS `t2_c2`
FROM
    `o_codes` `t`
    LEFT OUTER JOIN `achievements` `positive` ON (`positive`.`achievement_id` = `t`.`order_product_id`)
    LEFT OUTER JOIN `deductions` `deduction` ON (`deduction`.`deduction_id` = `t`.`order_product_id`)
WHERE
    t.myuser_id = 12345;

My output example...

t0_c1 | t0_c2  |   reason   | t1_c0 
1234  | 11481  | Good Stuff | (NULL)
1477  | 11482  |   (NULL)   | (NULL)

How can I alter this query so the second row which has a value of NULL for the reason column is set to 'CUSTOM' while taking into account the current IF statement is still required - any ideas?

Upvotes: 0

Views: 3606

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

The IF part should be

COALESCE(
IF (
positive.reason IS NOT NULL,
positive.reason,
deduction.reason
),'CUSTOM')
 AS reason,

Upvotes: 0

Shane
Shane

Reputation: 790

Use IFNULL:

IFNULL(expr1, 0)

Reference:

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. '0' in this case. IFNULL() returns a numeric or string value, depending on the context in which it is used.

IFNULL(`positive`.`achievement_id`, 0) AS `t1_c0`,

Upvotes: 1

Related Questions