mysql Result consisted of more than one row error-CodePudding

when I execute this query I got this error message "Error Code: 1172. Result consisted of more than one row"

CREATE DEFINER=`root`@`localhost` PROCEDURE `un_follow`(
  user_been_following_id int,
  user_following_id int
)
BEGIN
    declare id int;
    select following_id into id from user_following
        where user_been_following_id = user_been_following_id
        and  user_following_id =  user_following_id; 
        
    delete from user_following 
    where following_id = id;
END

id is the primary key of following table any help?

CodePudding user response：

Your local variable has the same name as the table column. That way you are never comparing the local variable against the column but always the local variable against the local variable itself.

Your query needs to return exactly one row to feed the id variable

select following_id into id from user_following
    where user_been_following_id = user_been_following_id
    and  user_following_id =  user_following_id;

both user_been_following_id and user_following_id are interpreted as local variables in all instances so this translates like

select following_id into id from user_following
    where 1 = 1
    and  1 = 1;

where all rows of user_following are returned. To fix this rename your local variables like

CREATE DEFINER=`root`@`localhost` PROCEDURE `un_follow`(
  local_user_been_following_id int,
  local_user_following_id int
)
BEGIN
    declare id int;
    select following_id into id from user_following
        where user_been_following_id = local_user_been_following_id
        and  user_following_id =  local_user_following_id; 
    
    delete from user_following 
    where following_id = id;
END

(assuming you don't have a column called local_user_been_following_id or local_user_following_id on table user_following)