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