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

http://stackoverflow.com/questions/6081436/how-to-use-alias-as-field-in-mysql/6081523#6081523 — Ravi Parekh, Aug 31 '16 at 09:34

score 180 · Accepted Answer · answered Apr 09 '12 at 07:50

180

Surround that select with parentheses.

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

answered Apr 09 '12 at 07:50

Sergio Tulentsev

226,338
43
373
367

22

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) ` – Shafizadeh Jun 29 '16 at 22:40
1

@RajatGupta: where are you running it? And define "not working". – Sergio Tulentsev May 11 '17 at 09:56
5

@Shafizadeh: yep, that subquery should return just one row and column – Sergio Tulentsev May 11 '17 at 09:57
Does not work for me, tried on phpmyadmin mysql. An error appears saying "A new instruction was found, but there is no delimiter between it and the previous" (translated from german). – Black Sep 06 '18 at 09:40
1

@Black: perhaps, some of the other answers will work. – Sergio Tulentsev Sep 06 '18 at 14:32
3

It seems that the return value has to be one, not a list of values – Victor S Nov 07 '18 at 21:36
How to declare @v1 in a stored procedure? What would be the type? – Dev1ce Jun 26 '19 at 08:14
im having a login query but it wont work ```:SET @res := ( SELECT concat_ws(' ', users.first_name, users.last_name), COUNT(roles.id) AS role_count FROM user_roles JOIN users on users.id = user_roles.user_id JOIN roles on roles.id = user_roles.role_id GROUP BY users.id ); SELECT @res; – Faraz salehi Dec 08 '21 at 15:56
how to add if statement IF @v1 = 10 THEN SELECT "TEN" ELSE SELECT "NOT TEN" END IF – Arif Hidayat Dec 17 '21 at 14:49

Yirkha · Answer 2 · 2015-04-01T13:37:26.267

45

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 01 '15 at 13:37

answered Apr 25 '14 at 00:19

Yirkha

12,737
5
38
53

Aman Maurya · Answer 3 · 2021-04-23T07:01:01.047

12

use this

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

tested and workes fine...

edited Apr 23 '21 at 07:01

answered Apr 27 '14 at 22:45

Aman Maurya

1,305
12
26

score 5 · Answer 4 · edited Dec 17 '19 at 13:31

5

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

edited Dec 17 '19 at 13:31

AgeDeO

3,137
2
25
57

answered Dec 17 '19 at 10:37

Kesha Viveki

169
2
2

score 0 · Answer 5 · answered Aug 08 '23 at 08:56

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

DROP TEMPORARY TABLE IF EXISTS myDb.temp_variable;
CREATE TEMPORARY TABLE myDb.temp_variable SELECT * FROM myDb.student ORDER BY id DESC LIMIT 25;
SELECT * FROM myDb.temp_variable;

DROP TEMPORARY TABLE IF EXISTS myDb.temp_variable;

How to store Query Result in variable using mysql

5 Answers5

Linked