1

I have been trying to perform a bulk update using a single update query but unable to do so within the stored procedure. Following is the bulk update query that run flawlessly

UPDATE `batch_requests_detail`
SET `processing_status` = 5
WHERE `batch_requests_detail_id`  IN (1,2,3);

Where as when I use it within the stored procedure it didn't work

CALL Update_Batch_Request_Detail_Bulk('1,2,3',10);

CREATE DEFINER=`root`@`%` PROCEDURE `Update_Batch_Request_Detail_Bulk`(
IN _batch_request_detail_ids VARCHAR(5000),
IN _processing_status INT
)
BEGIN
    UPDATE `batch_requests_detail`
    SET `processing_status` = _processing_status
    WHERE `batch_requests_detail_id`  IN (_batch_request_detail_ids);
    END$$

DELIMITER ;
1

Assuming that _batch_request_detail_ids = "1,2,3", try this :

CREATE DEFINER=`root`@`%` PROCEDURE `Update_Batch_Request_Detail_Bulk`(
IN _batch_request_detail_ids VARCHAR(5000),
IN _processing_status INT
)
BEGIN
     /**************** Set statement added***********/
    SET @sql =concat("UPDATE batch_requests_detail
    SET processing_status = ",_processing_status," 
    WHERE batch_requests_detail_id  IN (",_batch_request_detail_ids,");");
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    END$$

DELIMITER ;
  • Thanks, i tried this but Concat isn't working. I tried using single quote like this @sql = CONCAT('UPDATE batch_requests_detail SET processing_status = ', _processing_status , ' WHERE batch_requests_detail_id IN (' , _batch_request_detail_ids , ');'); But it didn't work too. – hellowahab Mar 4 '17 at 13:17
  • 1
    ok got it Set statement was missing. – hellowahab Mar 4 '17 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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