主页 / dba / 问题 / 166184
Accepted
hellowahab
Asked: 2017-03-05 01:10:50 +0800 CST

如何在 mysql 存储过程中执行构建更新

  • 772

我一直在尝试使用单个更新查询执行批量更新，但无法在存储过程中执行此操作。以下是完美运行的批量更新查询

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

当我在存储过程中使用它时它不起作用

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 ;
mysql bulk

1 个回答

  1. Best Answer

    假设 _batch_request_detail_ids = "1,2,3"，试试这个：

    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 ;
    • 1

相关问题