0

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 ?

6
  • Learn how to use the single statement INSERT ... ON DUPLICATE KEY UPDATE ... instead of select+insert/update.
    – Rick James
    Dec 6, 2016 at 20:22
  • Thank you. But I was tried to be more readable. I don't want to id = (SELECT long WHERE clause...)
    – jeefo
    Dec 6, 2016 at 20:29
  • But IODKU does not need any subqueries.
    – Rick James
    Dec 6, 2016 at 20:40
  • I think IODKU is required primary key or unique key. There is only primary key id auto increment. That is why I need to select id = (subquery)
    – jeefo
    Dec 6, 2016 at 20:44
  • According to your SELECT, you need a 6-column unique key (origin...days).
    – Rick James
    Dec 6, 2016 at 21:46

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.