MySQL user-defined variable in WHERE clause

mysql where-clause
Paulo Freitas picture Paulo Freitas · Oct 21, 2010 · Viewed 35.8k times · Source

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.

Answer

Maxym picture Maxym · May 16, 2011

Not far from what Mike E. proposed, but one statement:

SELECT id, location FROM songs, ( SELECT @id := 10 ) AS var WHERE id = @id;

I used similar queries to emulate window functions in MySQL. E.g. Row sampling - just an example of using variables in the same statement

Related questions

SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying …

Read More
SQL WHERE condition is not equal to?

Is it possible to negate a where clause? e.g. DELETE * FROM table WHERE id != 2;

Read More
Detect if value is number in MySQL

Is there a way to detect if a value is a number in a MySQL query? Such as SELECT * FROM myTable WHERE isANumber(col1) = true

Read More