Don't update if null is given in a procedure

Question

I know an around-about way of accomplishing this but I would like to know the clean and best way to solve my problem. I am using an INSERT INTO with an ON DUPLICATE KEY UPDATE. Sometimes a value is not given but I still have to pass it into the parameter of the procedure otherwise it would fail. So I have been passing in a null value but this will update the field with nulls and I will lose data. So, I would like to "ignore" a field if it a null value gets passed into it. In other words just not update it or get the current value instead and pass that in.

I could use multiple IF statements to just check if a value is null or not but this procedure is about 20 values long and that would seem ridiculous and gratuitous. If there is a better way, I know that it can be done differently.

I'm only going to include part of my procedure for simplicity sake.

PROCEDURE `p_my_record_create`(
    IN in_group varchar(255),
    IN in_package varchar(255),
    IN in_type enum('A', 'M'),
    IN in_uid varchar(255),
    IN in_member_id int(11),
    IN in_first_name varchar(255)
)
BEGIN
    INSERT INTO myDatabase.my_record
    (`group`, `package`, `type`, `uid`, `member_id`, `first_name`)
    VALUES
    (in_group, in_package, in_type, in_uid, in_member_id, in_first_name)
    ON DUPLICATE KEY UPDATE
        `group` = in_group,
        `package` = in_package,
        `type` = in_type,  #if this is passed in as null then I would like for it to be "ignored" or if any of them are.
        `uid` = in_uid,
        `client_member_id` = in_client_member_id,
        `first_name` = in_first_name;

    SELECT
        record_id
    FROM
        myDatabase.my_record
    WHERE
        record_id = LAST_INSERT_ID();
END

If there is a simple way to accomplish this in MySQL, please enlighten me that would really help. Thanks.

Maybe [`COALESCE()`](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_coalesce) would shorten it ? — AymDev, May 28 '19 at 20:25
@AymDev that would be handy like used [here](https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null), but if I'm using the ON DUPLICATE KEY UPDATE how do I get the primary key of the table beforehand in order to set the value for the COALESCE, though? — A. Dady, May 28 '19 at 20:30
That'd exactly be like this post, yes. Wouldn't `\`type\` = COALESCE(in_type, \`type\`)` work ? I'm not so used to procedures, give it a try maybe ? — AymDev, May 28 '19 at 20:33

score 1 · Accepted Answer · answered May 28 '19 at 20:38

PROCEDURE `p_my_record_create`(
    IN in_group varchar(255),
    IN in_package varchar(255),
    IN in_type enum('A', 'M'),
    IN in_uid varchar(255),
    IN in_member_id int(11),
    IN in_first_name varchar(255)
)
BEGIN
    INSERT INTO myDatabase.my_record
    (`group`, `package`, `type`, `uid`, `member_id`, `first_name`)
    VALUES
    (in_group, in_package, in_type, in_uid, in_member_id, in_first_name)
    ON DUPLICATE KEY UPDATE
        `group` = in_group,
        `package` = COALESCE(in_package, `package`),
        `type` = COALESCE(in_type, `type`),
        `uid` = in_uid,
        `client_member_id` = in_client_member_id,
        `first_name` = COALESCE(in_first_name, `first_name`);

    SELECT
        record_id
    FROM
        myDatabase.my_record
    WHERE
        record_id = LAST_INSERT_ID();
END

Tiny addition: I do not like to repeat myself and mention `in_group` & Co in both parts of the insert/on duplicate key update query. So I prefer to use ```VALUES(`group`)``` & Co instead. So it would be ```... ON DUPLICATE KEY UPDATE `group` = VALUES(`group`), `type` = COALESCE(VALUES(`type`), `type`) ...``` — fifonik, May 28 '19 at 21:50

Don't update if null is given in a procedure

1 Answers1