CODEWITHSUNDEEP
mysql
A. Dady
A. Dady

Reputation: 143

Don't update if null is given in a procedure

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.

Upvotes: 0

Views: 53

Answers (1)

A. Dady
A. Dady

Reputation: 143

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

Upvotes: 1

Related Questions