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.
T. Zengerink
4,2195 gold badges29 silver badges31 bronze badges
asked Oct 21, 2010 at 3:14
2
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
user222758
12.7k13 gold badges72 silver badges95 bronze badges
answered May 16, 2011 at 18:31
MaxymMaxym
11.8k3 gold badges43 silver badges47 bronze badges
2
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;
answered Aug 17, 2012 at 18:02
steampoweredsteampowered
11.4k12 gold badges72 silver badges96 bronze badges
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.
answered Oct 21, 2010 at 3:27
Mike E.Mike E.
4515 silver badges6 bronze badges
0
This worked for me!
SET @identifier = 7;
SELECT * FROM test where identifier = @identifier;
answered Dec 1, 2016 at 11:32
JonasJonas
493 bronze badges
1
I want to take customized date and time value in a variable then i want to use it in where clause as a condition.
For that what i did is, using 2 select statments and inserting into 2 variables respectivly.its storing in that vaiable successfully.
when i use those variable in where clause[sql query] its not giving output and if i hardcode those vaiable values output will come.
Please help me to overcome this issue.The way i used this variable is correct in where clauese or how to use it in correct way
What I have tried:
select concat[DATE_SUB[CURDATE[], INTERVAL 1 DAY],' ','00:00:00'] into @fromdate; select concat[DATE_SUB[CURDATE[], INTERVAL 1 DAY],' ','24:59:59'] into @todate; SELECT * FROM cust_item_mst gsk JOIN u_stockiest_item gsk_mapping ON gsk_mapping.c_stockiest_code = gsk.c_c2code AND gsk_mapping.c_stockiest_item_code = gsk.c_code LEFT JOIN u_stockiest_item AS stk_ucode ON stk_ucode.c_ucode = gsk_mapping.c_ucode LEFT JOIN cust_inv_det dist_inv ON dist_inv.c_c2code = stk_ucode.c_stockiest_code AND stk_ucode.c_stockiest_item_code = dist_inv.c_item_code JOIN cust_inv_mst inv_mst ON inv_mst.c_c2code = dist_inv.c_c2code AND inv_mst.c_br_code = dist_inv.c_br_code AND inv_mst.c_year = dist_inv.c_year AND inv_mst.c_prefix = dist_inv.c_prefix AND inv_mst.n_srno = dist_inv.n_srno LEFT JOIN cust_act_mst retail_mst ON retail_mst.c_c2code = inv_mst.c_c2code AND retail_mst.c_code = inv_mst.c_cust_code JOIN cust_inv_sub_det invrcvd ON invrcvd.c_c2code = inv_mst.c_c2code AND invrcvd.c_br_code = inv_mst.c_br_code AND invrcvd.c_year = inv_mst.c_year AND invrcvd.c_prefix = inv_mst.c_prefix AND invrcvd.n_srno = inv_mst.n_srno join gsk_csv_stkdata gsk_stk on gsk_stk.lcc2code=stk_ucode.c_stockiest_code WHERE gsk.c_c2code = '00S204' AND gsk_stk.lcc2code = '003004' AND invrcvd.d_ldate >= @fromdate AND invrcvd.d_ldate = @fromdate AND invrcvd.d_ldate = '+@fromdate+' AND invrcvd.d_ldate