Menu
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.
268
MySQL variable assignment There are two ways to assign a value to a user-defined variable. You can use either := or = as the assignment operator in the SET statement. For example, the statement assigns number 100 to the variable @counter. The second way to assign a value to a variable is to use the SELECT statement.
Mysql also supports the concept of User-defined variables, which allows passing of a value from one statement to another. A user-defined variable in Mysql is written as @var_name where, var_name is the name of the variable and can consist of alphanumeric characters, ., _, and $.
User variables are written as @ var_name , where the variable name var_name consists of alphanumeric characters, . , _ , and $ . A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var' , @"my-var" , or @`my-var` ). User-defined variables are session specific.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
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
129
From the MySQL manual page on User Defined Variables:
As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.
So you should separate the assignment from the select statement:
SET @id = 10; SELECT id, location, @id FROM songs WHERE id = @id; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
