mysqlvariablesmysql-variables

MySql use variable in WHERE

When I write code:

SET @code = 'a123';
UPDATE my_table SET number = (number + 1) WHERE code = @code;

it doesn't work correctly. But if I write code:

UPDATE my_table SET number = (number + 1) WHERE code = 'a123';

it works correctly. I want to use variable in "WHERE". how can I use it?

Solution

  • After several tests I came to the conclusion that I should to change the code like this:

    SET @code = 'a123';
SET @mid = NULL;
SELECT @mid := id, @code := code FROM my_table WHERE code = @code;
UPDATE my_table SET number = (number + 1) WHERE id = @mid;

    I should use 'id' instead of 'code' after 'WHERE' in UPDATE transaction

    also I should to set @code in SELECT transaction again (why? I don't know!)

    This code works correctly and the previous code doesn't work correctly and I don't know the reason!