CODEWITHSUNDEEP
mysqlsqlstored-procedures
Hooman Bahreini
Hooman Bahreini

Reputation: 15559

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
) 
BEGIN
    IF ImportedProductId <= 0 THEN
        INSERT INTO ImportedProduct(UniqueThirdPartyCode, BranchId)
        VALUES(UniqueThirdPartyCode, BranchId);
    ELSE
        UPDATE 
            ImportedProduct
        SET
            UniqueThirdPartyCode = UniqueThirdPartyCode, 
            BranchId = BranchId
        WHERE  
            ImportedProductId = ImportedProductId;
    END IF;
END //

DELIMITER ;

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

CALL AddOrUpdateImportedProduct (1, 'y-105', 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

Upvotes: 0

Views: 28

Answers (1)

Islingre
Islingre

Reputation: 2349

I am pretty sure ImportedProductId = ImportedProductId holds always.. Perhaps rename your variable or add an alias to the updated table.

Upvotes: 1

Related Questions