My procedure deletes all rows when I use delete statement

88
November 21, 2018, at 10:30 PM

That's my table:

create table if not exists Ditte(nome varchar(30), luogo varchar(30), ZIP int);

That's my procedure:

delimiter // 
create procedure deleteDitta(zip int)
begin
DECLARE newZIP int;
SET newZIP = zip;
DELETE from Ditte where ZIP = newZIP;
end;
// 
delimiter ;

This is what I added in my table:

insert ignore into Ditte values ("Ditta1", "city1", 6828);
insert ignore into Ditte values ("Ditta2", "city2", 12345);

When I call my procedure deleteDitta and I put "12345" as parameter (like this: call deleteDitta(12345);), the procedure should deletes only the second row in table "Ditte", but it deletes all the contents of the table. How can I fix it?

Answer 1

This seems to be MySQL getting confused about column names and variable names. Changing your procedure to this fixes the problem:

create procedure deleteDitta(dzip int)
begin
    DELETE from Ditte where ZIP = dzip;
end;

Demo on dbfiddle

READ ALSO
Join self table and get values in new rows

Join self table and get values in new rows

I wonder if there is a way to JOIN a self table and then get those values in new rows

88
Alternative to a different MYSQL table for each file my user uploads? [on hold]

Alternative to a different MYSQL table for each file my user uploads? [on hold]

I built an application for personal use that I am now looking to sell commerciallyThe application is essentially a tool that accepts a CSV file with anywhere from 10k-270k rows and then generates a report using the information inside

69
Mysql select based on other select

Mysql select based on other select

dears i have below query that's gets the count based on cases and it's working fine

111