Mysql use variable in where clause

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 <= @todate
order by gsk_stk.lcc2code       

Solution 1

It should be:

WHERE
gsk.c_c2code = '00S204'
   AND gsk_stk.lcc2code = '003004'
   AND invrcvd.d_ldate >= @fromdate
   AND invrcvd.d_ldate <= @todate

As you can see, i removed unnecessary signs: '+ +' around the variables.

For further details, please see: MySQL Variables[^]

[EDIT]

Quote:

first i tried without + sign still it did not come

So, try to set value to the variable different way:

SET  @fromdate:='2019-08-30 00:00:00'
--or
SELECT @fromdate:=MAX(dateTimeField)
FROM yourtable;

Updated 29-Aug-19 20:24pm

Comments

Solution 2

Look at your code:

WHERE
gsk.c_c2code = '00S204'
   AND gsk_stk.lcc2code = '003004'
   AND invrcvd.d_ldate >= '+@fromdate+'
   AND invrcvd.d_ldate <= '+@todate+'
order by gsk_stk.lcc2code

You aren't using the variable content - you have enclosed them in a string, so the string is what the comparison is made with.
Try:

WHERE
gsk.c_c2code = '00S204'
   AND gsk_stk.lcc2code = '003004'
   AND invrcvd.d_ldate >= @fromdate
   AND invrcvd.d_ldate <= @todate
order by gsk_stk.lcc2code

Comments

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900

Can I use variable in WHERE clause SQL?

How do I use variables in MySQL?

Can we use if condition in WHERE clause in MySQL?

How do I use a variable in SQL SELECT statement?