MySQL user-defined variable in WHERE clause

Question

I want to know if there is a way to use a user-defined variable in WHERE clause, as in this example:

SELECT id, location, @id := 10 FROM songs WHERE id = @id

This query runs with no errors but doesn't work as expected.

Thank you in advance.

Answer 1

Sure, but I've never seen anyone try to set a variable and use it in the same statement like you are. Try:

SET @id := 10;
SELECT @id := 10 FROM songs WHERE id = @id;

or

SELECT @id := 10 FROM songs;
SELECT @id := 10 FROM songs WHERE id = @id;

I've used both, and they both seem to work for me.