CODEWITHSUNDEEP
mysql
Lee
Lee

Reputation: 8734

MySQL update where clause is being ignored

Here is my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `User_Confirm`(id INT)
BEGIN
  UPDATE 
    `User` 
  SET
    ConfirmedAt = UTC_TIMESTAMP() 
  WHERE `Id` = id ;
END$$

If I run 

`CALL `User_Confirm`(19);`

I get this message saying the whole table has been updated:

1 queries executed, 1 success, 0 errors, 0 warnings

Query: call `User_Confirm`(19)

11 row(s) affected

Execution Time : 0.040 sec
Transfer Time  : 0.145 sec
Total Time     : 0.186 sec

User 19 does exist, why is this happening

UPDATE

If I run this extraction from the stored procedure only one row gets updated as expect, so it definitely has something to do with the stored procedure:

 UPDATE 
    `User` 
  SET
    ConfirmedAt = UTC_TIMESTAMP() 
  WHERE `Id` = 19 ;

Upvotes: 3

Views: 1414

Answers (4)

Meow
Meow

Reputation: 138

MySQL is somethings* not case sensitive, would that be the reason?

Upvotes: 1

spencer7593
spencer7593

Reputation: 108380

I think MySQL is ignoring your input argument, because the name matches the name of a column in one of the tables referenced in the UPDATE statement.

Let me rephrase that...

you think your predicate is of the form:

WHERE col = input_argument

But MySQL is actually seeing your predicate as:

WHERE col = col

or, as

WHERE input_argument = input_argument

The latter will be true for EVERY row in the table, the former will be true for every row that has a a non-null value stored in col. (A simple test will reveal whether it's the column reference takes precedence, or a variable reference takes precedence. I do know that in Oracle, DML statements in PL/SQL blocks, column references take precedence over PL/SQL variables, and there is NO warning.)

A quick test would be rename the input argument, so that it doesn't match any column name.

Another workaround may be to use a MySQL user variable, (be careful of implicit datatype conversions), e.g.

CREATE PROCEDURE `User_Confirm`(parameterId INT)
BEGIN
  SET @argId := parameterId;
  UPDATE `User` 
     SET ConfirmedAt = UTC_TIMESTAMP()
   WHERE `Id` = @argId ;
END$$

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Your problem is that the parameter name to the stored procedure is the same as the column name. It is a good idea to prefix parameter names with something obvious. Try this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `User_Confirm`(param_id INT)
BEGIN
  UPDATE `User` 
      SET ConfirmedAt = UTC_TIMESTAMP() 
      WHERE `Id` = param_id ;
END$$

Upvotes: 4

Axel Amthor
Axel Amthor

Reputation: 11096

probably self referencing on id, try:

.... WHERE `USER`.`Id` = id ;

Upvotes: 3

Related Questions