MySql use variable in WHERE

Question

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?

check this answer https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql — Marwen Jaffel, Jan 15 '20 at 09:08
Is your SET & UPDATE queries within same transaction? If it does not try to wrap them into START TRANSACTION; SET ...; UPDATE ...; COMMIT; — Slava Rozhnev, Jan 15 '20 at 12:09
What do you mean under "it does not work"? Throws error? Not updating record? — fifonik, Jan 15 '20 at 21:33

score 0 · Accepted Answer · answered Jan 19 '20 at 04:57

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!

MySql use variable in WHERE

1 Answers1