mercredi 6 novembre 2019

Unable to diagnose the problem with MySQL stored procedure

I have defined the following stored procedure to add/update a table called ImportedProduct.

If the primary key, ImportedProductId is provided and is greater than zero, then update the exiting record otherwise insert a new one:

DELIMITER //

CREATE PROCEDURE AddOrUpdateImportedProduct (
    IN ImportedProductId BIGINT,
    IN UniqueThirdPartyCode VARCHAR(64),
    IN BranchId BIGINT,
    IN AdChecksum VARCHAR(8),
    IN AdBaseId BIGINT
) 
BEGIN
    IF ImportedProductId <= 0 THEN
        INSERT INTO ImportedProduct(AdBaseId, UniqueThirdPartyCode, BranchId, AdChecksum)
        VALUES(AdBaseId, UniqueThirdPartyCode, BranchId, AdChecksum);
    ELSE
        UPDATE 
            ImportedProduct
        SET
            AdBaseId = AdBaseId, 
            UniqueThirdPartyCode = UniqueThirdPartyCode, 
            BranchId = BranchId, 
            AdChecksum = AdChecksum
        WHERE  
            ImportedProductId = ImportedProductId;
    END IF;
END //

DELIMITER ;

Now I run the following code to update an existing row:

CALL AddOrUpdateImportedProduct (1, '105', 1, '14a76d13', 24);

I can see that the record with with ImportedProductId = 1 exists in the table, but I am getting the following error:

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode

Aucun commentaire:

Enregistrer un commentaire