How to store Query Result in variable using mysql

Question

SET @v1 := SELECT COUNT(*) FROM user_rating;
SELECT @v1

When I execute this query with set variable this error is shown.

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'SELECT count(*) FROM user_rating' at line 1

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

(1 row(s) returned)
Execution Time : 00:00:00:343
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:343

stackoverflow.com/questions/6081436/…

Ravi Parekh
– Ravi Parekh

2016-08-31 09:34:15 +00:00
Commented Aug 31, 2016 at 9:34 — Ravi Parekh
– Ravi Parekh, Commented Aug 31, 2016 at 9:34

Sergio Tulentsev · Accepted Answer · 2012-04-09 07:50:38Z

184

Surround that select with parentheses.

SET @v1 := (SELECT COUNT(*) FROM user_rating);
SELECT @v1;

answered Apr 9, 2012 at 7:50

Sergio Tulentsev

231k43 gold badges381 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Shafizadeh Over a year ago

Should that sub query just be containing 1 row and 1 column? 1. #1242 - Subquery returns more than 1 row , 2. #1241 - Operand should contain 1 column(s)

Sergio Tulentsev Over a year ago

@RajatGupta: where are you running it? And define "not working".

Sergio Tulentsev Over a year ago

@Shafizadeh: yep, that subquery should return just one row and column

Sergio Tulentsev Over a year ago

@Black: perhaps, some of the other answers will work.

Victor S Over a year ago

It seems that the return value has to be one, not a list of values

|

Yirkha · Accepted Answer · 2015-04-01 13:37:26Z

46

Additionally, if you want to set multiple variables at once by one query, you can use the other syntax for setting variables which goes like this: SELECT @varname:=value.

A practical example:

SELECT @total_count:=COUNT(*), @total_price:=SUM(quantity*price) FROM items ...

edited Apr 1, 2015 at 13:37

answered Apr 25, 2014 at 0:19

Yirkha

14k5 gold badges43 silver badges56 bronze badges

Comments

Aman Maurya · Accepted Answer · 2021-04-23 07:01:01Z

12

use this

 SELECT weight INTO @x FROM p_status where tcount='value' LIMIT 1;

tested and workes fine...

edited Apr 23, 2021 at 7:01

answered Apr 27, 2014 at 22:45

Aman Maurya

1,32512 silver badges26 bronze badges

Comments

AgeDeO · Accepted Answer · 2019-12-17 13:31:22Z

8

Select count(*) from table_name into @var1; 
Select @var1;

edited Dec 17, 2019 at 13:31

AgeDeO

3,1572 gold badges27 silver badges58 bronze badges

answered Dec 17, 2019 at 10:37

Kesha Viveki

2193 silver badges2 bronze badges

Comments

zhuhang.jasper · Accepted Answer · 2023-11-09 04:00:42Z

2

Use MySQL CREATE TEMPORARY TABLE if you want to save query result as a temp table for reuse in subsequent queries.

See: https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html

-- begin: drop first just in case
DROP TEMPORARY TABLE IF EXISTS myDb.temp_variable; 
CREATE TEMPORARY TABLE myDb.temp_variable SELECT * FROM myDb.student ORDER BY id DESC LIMIT 25;

-- your scripts
SELECT * FROM myDb.temp_variable;

-- end: drop to prevent cached values accessed by other queries
DROP TEMPORARY TABLE IF EXISTS myDb.temp_variable;

edited Nov 9, 2023 at 4:00

answered Aug 8, 2023 at 8:56

zhuhang.jasper

4,5844 gold badges23 silver badges33 bronze badges

Comments

zhuhang.jasper · Accepted Answer · 2023-11-09 04:21:50Z

1

If you're trying to use the variable inside SELECT... WHERE... IN (*here*).

Use GROUP_CONCAT and find_in_set.

-- initialise with a comma-separated string
SET @emails = '[email protected],[email protected],[email protected]';

-- search condition
SELECT * FROM customer WHERE find_in_set(email, @emails);

-- or, you can initialise the variable using query results
SELECT GROUP_CONCAT(phone) INTO @activeCustomerPhones FROM customer WHERE status = 'ACTIVE';

SELECT * FROM transaction WHERE find_in_set(phone, @activeCustomerPhones);

/* 
customer:
- phone
- email
- status 

transaction:
- phone
*/

edited Nov 9, 2023 at 4:21

answered Nov 9, 2023 at 4:16

zhuhang.jasper

4,5844 gold badges23 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to store Query Result in variable using mysql

6 Answers 6

9 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

9 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related