0

I am trying to make a simple project for college assignment. In that,When I am trying to delete a record from a stored procedure in MySQL, that delete statement is deleting all the records.

Here is the code :

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE 
           `removebus`(in busnumber int,out message varchar(255))
BEGIN

    delete from fare where busnumber = busnumber;
    delete from bus where busnumber = busnumber;
    set message = 'success';

    else
        set message = 'not found';
    end if;

END

And I am executing like call removebus(1,@message);.

When I am trying to only execute the delete statement from command window, then it is deleting one record only but when I executed the call statement, all the records are deleted from my table. Just to add, busnumber is primary key of bus table. I am not able to understand why.

halfer
  • 19,824
  • 17
  • 99
  • 186
user976083
  • 51
  • 2
  • 5

3 Answers3

1

Well, as far as I can see, busnumber is always equals busnumber... You might as well write "1 = 1".

Change the variable name - that should work for you.

Alexander
  • 3,129
  • 2
  • 19
  • 33
1

Two things. First delete is not working as truncate table. It might have the same results, but the performance is much, much different. delete logs all its transactions; truncate table does not.

Second, get into the habit of naming parameter arguments with a special prefix, such as:

CREATE DEFINER=`root`@`localhost` PROCEDURE 
           `removebus`(in in_busnumber int, out out_message varchar(255))
. . .

If you don't get into this habit, you are likely to continue making the same mistake for a long time (at least, that is my experience).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You are running

delete from fare where busnumber = busnumber;

for every row in the table busnumber will equal itself, therefor they will all be deleted.

Try changing the input variable to something that doesn't clash with the column name.

Brenton Alker
  • 8,947
  • 3
  • 36
  • 37