CODEWITHSUNDEEP
mysqlstored-procedures
usumoio
usumoio

Reputation: 3568

Stored procedure behaves differently from regular query

I had a problem where I executed a store procedure that does an update on a few tables in the database. The error was done such that only a single field (representing the same record id) is updated to a new id each time. This is the spoc:

DROP PROCEDURE IF EXISTS feeds_transfer;
DELIMITER $$
CREATE PROCEDURE feeds_transfer(IN original_owner INT, IN new_owner INT, IN feed_id INT)
    BEGIN
        UPDATE `events` SET `user_id` = new_owner WHERE `user_id` = original_owner AND `feed_id` = feed_id;
        UPDATE `feeds` SET `partner_id` = new_owner WHERE `partner_id` = original_owner AND `id` = feed_id;
    UPDATE `ics_uploads` SET `user_id` = new_owner WHERE `user_id` = original_owner AND `context_type` = 'feed' AND `context_id` = feed_id;
    UPDATE `images` SET `user_id` = new_owner WHERE `user_id` = original_owner AND `context` = 'feed' AND `context_id` = feed_id;
    UPDATE `private_feed_invitees` SET `user_id` = new_owner WHERE `user_id` = original_owner AND `feed_id` = feed_id;
        UPDATE `subscribed_feeds` SET `user_id` = new_owner WHERE `user_id` = original_owner AND `feed_id` = feed_id;
    END $$
DELIMITER ;

What happens is that the subscribed_feeds table will update a every record where user_id = original_owner. Its like its ignoring the feed_id = feed_id

Upvotes: 0

Views: 109

Answers (1)

usumoio
usumoio

Reputation: 3568

Okay so what is happening here is not so much that the feed_id = feed_id clause is being ignored, its that it is always true, and so it always triggers more updates than hoped. The moral of this story is that you should NEVER name the input variables to your stored procedure the same things as any of the fields you plan to do logical checks against.

Upvotes: 1

Related Questions