Stored Procedure updates two rows

Question

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.

I guess that in real example you don't have hardcoded UPDATE USR_PNT_SUMM SET USD_PNTS = USD_PNTS + PNTS WHERE USER_ID = 1; but proper parameters. — Lukasz Szozda
– Lukasz Szozda, Commented Aug 27, 2017 at 16:54
I put USER_ID as 1 so as to make sure that only the row for user id 1 gets updated. But I still see other row getting updated. — user3276247
– user3276247, Commented Aug 27, 2017 at 17:15

Lukasz Szozda · Accepted Answer · 2017-08-27 16:53:07Z

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.

This is right on the spot. I wonder how I did not notice this! Thanks a lot though.

Collectives™ on Stack Overflow

Stored Procedure updates two rows

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related