CODEWITHSUNDEEP
mysql
Jairo Lozano
Jairo Lozano

Reputation: 4511

MySQL Error Code 1175 when trying to update inside stored procedure

I'm creating a stored procedure to update a table record based on record id. Here is my code (values in the update section are hardcoded just for tests):

CREATE DEFINER=`root`@`localhost` PROCEDURE `destination_update`(
 IN destination_id char(3),
 IN destination_name varchar(250),
 IN is_office tinyint(1),
 IN address varchar(250),
 IN status_id int(11))

BEGIN
    UPDATE `paisanos_new`.`destinations`
    SET
    `destination_name` = 'b',
    `is_office` = 0,
    `address` = 'b',
    `status_id` = 2
    WHERE `destination_id` = 'zzz';

END

When I call the procedure with:

call paisanos_new.destination_update('zzz', 'a', 0, 'a', 2);

I get the following error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

However, when I execute just the update query it works fine.

I've tried disabling SQL_SAFE_UPDATES but then when I run the stored procedure it updates all my table records! just as if, indeed, I didn't have a where clause with a primary key.

Any ideas?

Upvotes: 2

Views: 1637

Answers (2)

sdsc81
sdsc81

Reputation: 570

When you excecute just the update query, it works becuase it finds the column in the table named destination_id.

However, inside the procedure, the destination_id that is considered, is the one in the paremeter, so your query actually is like:

UPDATE ... WHERE 'zzz' = 'zzz';

Wich is the same of doing WHERE true, so it updates all the rows.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Name your parameters in a stored procedure differently from columns. I'm guessing this will return an error:

CREATE DEFINER=`root`@`localhost` PROCEDURE `destination_update`(
 IN in_destination_id char(3),
 IN in_destination_name varchar(250),
 IN in_is_office tinyint(1),
 IN in_address varchar(250),
 IN in_status_id int(11)
)
BEGIN
    UPDATE `paisanos_new`.`destinations` d
    SET `destination_name` = 'b',
         `is_office` = 0,
         `address` = 'b',
         `status_id` = 2
    WHERE d.`destination_id` = 'zzz';    
END;

That is, the destination_id in the WHERE clause may be referencing the input parameter, rather than the column (which I'm guessing does not exist).

Upvotes: 1

Related Questions