CODEWITHSUNDEEP
mysqldatabasestored-proceduresmariadb
user3276247
user3276247

Reputation: 1084

Stored Procedure updates two rows

DELIMITER //
 CREATE OR REPLACE PROCEDURE GET_USER_PNTS(USER_ID INT , PNTS INT, QNT INT)
   BEGIN
   DECLARE x  INT DEFAULT 1;
   DECLARE TEMP_GIFT_ID INT;
   UPDATE USR_PNT_SUMM SET USD_PNTS = USD_PNTS + PNTS WHERE USER_ID = 1;
   COMMIT;
   END //
 DELIMITER ;

The above stored procedure updates two rows - one for user_id = 1 and the other one for userid 0. I dont understand why!

This is how I call the stored procedure - CALL GET_USER_PNTS(1, 1, 1)

Please let me know why the user_id 0 is also getting updated.

P.S 1. I am using MariaDB. 2. UserID 0 is what I had manually added in the table. In pratice there won't be any 0 user_id. But even then, the row should not have been updated.

Upvotes: 1

Views: 47

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

Please rename your parameters:

CREATE OR REPLACE PROCEDURE GET_USER_PNTS(L_USER_ID INT , L_PNTS INT, L_QNT INT)
   BEGIN
   DECLARE x  INT DEFAULT 1;
   DECLARE TEMP_GIFT_ID INT;
   UPDATE USR_PNT_SUMM SET USD_PNTS = USD_PNTS + L_PNTS WHERE USER_ID = L_USER_ID;
   COMMIT;
   END //

Probably USER_ID = USER_ID is treated as true.

Upvotes: 1

Related Questions