Mysql – How to update multiple rows without using JSON

MySQLupdate

I have a situation. In this example I want to update multiple notification definitions. Incoming data is very dynamic array object by user controlled. Sometimes new notifications added or removed.
My way of doing this is delete all unused notification definitions before insert new values. notifications.new_notification_definition procedure If given values are already exists in database update if not insert it. In this way I can update multiple rows. But I have a problem. It doesn't work. I don't know why sometimes new values are not inserted. Sometimes it does… Please help me what is best way to update multiple rows ? I have more problems like this.

DELETE FROM nd USING `notification_definitions` AS nd
WHERE
    `origin`     = 'table'       AND
    `type`       = 'date'        AND
    `table_name` = 'table_13' AND
    NOT EXISTS (
       SELECT NULL FROM $table WHERE `notification_definition_id` = nd.`id`
    );

UPDATE `notification_definitions` SET `is_active` = 'no' WHERE `table_name` = 'table_13';

CALL `notifications.new_notification_definition`('table_13', ?, ?, 123, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 2, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 7, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 30, ?, ?);

Procedure notifications.new_notification_definition

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `notifications.new_notification_definition`(
IN `in_table_name`          VARCHAR(100),
IN `in_primary_field_label` VARCHAR(250),
IN `in_field_label`         VARCHAR(250),
IN `in_days`                INT(11) UNSIGNED,
IN `in_color`               VARCHAR(20),
IN `in_message`             VARCHAR(250)
)
BEGIN

-- Select old definition
SELECT `id` INTO @id FROM `notification_definitions`
WHERE 
    `origin`              = "table"                  AND
    `type`                = "date"                   AND
    `table_name`          = `in_table_name`          AND
    `primary_field_label` = `in_primary_field_label` AND
    `field_label`         = `in_field_label`         AND
    `days`                = `in_days`
LIMIT 1;

IF @id IS NULL THEN
INSERT `notification_definitions` SET
    `origin`              = "table",
    `type`                = "date",
    `table_name`          = `in_table_name`,
    `primary_field_label` = `in_primary_field_label`,
    `field_label`         = `in_field_label`,
    `days`                = `in_days`,
    `color`               = `in_color`,
    `message`             = `in_message`,
    `created_at`          = NOW(),
    `updated_at`          = NOW();
ELSE 
UPDATE `notification_definitions` SET
    `color`      = `in_color`,
    `message`    = `in_message`,
    `is_active`  = 'yes',
    `updated_at` = NOW()
WHERE id = @id
LIMIT 1;
END IF;

END ;;
DELIMITER ;

EDIT, Fixed problem

While I was posting this question at night I was tired and didn't think well. Now I have a new question and still remaining main question too. Problem was here
SELECT id INTO @id

After I changed
SET @id = (SELECT id query) it solved, I don't know why? My guess is @id is never NULL after set. Last value is still used even row wasn't found. Can you explain to me why ?

I changed my strategy to update multiple rows.
First Update old rows field is_active = 'no' and INSERT ON DUPLICATE KEY UPDATE new values with is_active = 'yes' as James suggested. And lastly DELETE rows did not updated. Is there any better way to update multiple rows ?

Best Answer

I want to answer myself and close this question. Because I think I solved this problem. I appreciated James's comment for gave me an idea. In my current solution looks like this.

UPDATE `accessable_menu` SET `is_active` = 'no' WHERE `user_role_id` = $USER_ROLE_ID;

INSERT INTO `accessable_menu`(`user_role_id`, `menu_id`, `is_active`)
VALUES (inputs..., 'yes')
ON DUPLICATE KEY UPDATE `is_active` = 'yes';

DELETE FROM `accessable_menu` WHERE `user_role_id` = $USER_ROLE_ID AND `is_active` = 'no';

Update old rows field is_active = 'no' and insert new values on duplicate key update with is_active = 'yes'

Then delete all rows didn't updated. It might be better performance than delete all rows and insert again. Because it deletes only few rows.

Related Question