'Is is possible to use an IF in mysql subquery?

Trying to check if a name exists (in another table) and add to insert query in one step. I've discovered if the submitted empNum isn't in the User database, the query errors with: Column 'lastName' cannot be null. Can an if statement be used in the subquery something like below?

INSERT INTO Password_Reset (empNum, lastName, fromIP, dateReset)
    VALUES (34567, 
    IF(SELECT COUNT(*) FROM User WHERE empNum = 34567, SELECT lastName FROM User WHERE empNum = 34567, '---'),
    '192.168.1.1',
    NOW())
mysql


Solution 1:[1]

In this scenario, your best bet is to use the COALESCE operator.

INSERT INTO Password_Reset (empNum, lastName, fromIP, dateReset)
    VALUES (34567, 
    COALESCE((SELECT COUNT(*) FROM User WHERE empNum = 34567, SELECT lastName FROM User WHERE empNum = 34567), '---'),
    '192.168.1.1',
    NOW())

The IF function could be used, but it would be much more verbose:

INSERT INTO Password_Reset (empNum, lastName, fromIP, dateReset)
    VALUES (34567, 
    IF((SELECT COUNT(*) FROM User WHERE empNum = 34567, SELECT lastName FROM User WHERE empNum = 34567) IS NOT NULL, (SELECT COUNT(*) FROM User WHERE empNum = 34567, SELECT lastName FROM User WHERE empNum = 34567), '---'),
    '192.168.1.1',
    NOW())

Solution 2:[2]

you could use a case:

INSERT INTO Password_Reset (empNum, lastName, fromIP, dateReset)
SELECT 
    34567,
    CASE (EXISTS (SELECT * FROM User WHERE empNum = 34567))
        WHEN 1 THEN (SELECT lastName FROM User WHERE empNum = 34567)
        ELSE '---'
    END,
    '192.168.1.1',
    NOW()

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Julien May

Related Questions