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?

Is your SET & UPDATE queries within same transaction? If it does not try to wrap them into START TRANSACTION; SET ...; UPDATE ...; COMMIT; — Slava Rozhnev
– Slava Rozhnev, Commented Jan 15, 2020 at 12:09
What do you mean under "it does not work"? Throws error? Not updating record? — fifonik
– fifonik, Commented Jan 15, 2020 at 21:33

M. Feyz · Accepted Answer · 2020-01-19 04:57:23Z

0

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!

answered Jan 19, 2020 at 4:57

M. Feyz

3592 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql use variable in WHERE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related