32

This should be a simple syntax thing: I'm trying to set a variable in MySQL equal to the result of a query for instance:

SET @variable1 = SELECT salary FROM employee_info WHERE emp_id = 12345678;

Basically I want the salary from that employee to be stored as a variable that I can then manipulate and add.

What would the correct syntax for this be because I can't get it to work.

tshepang
  • 12,111
  • 21
  • 91
  • 136
NateSHolland
  • 1,130
  • 2
  • 11
  • 25
  • [Duplicate][1] [1]: http://stackoverflow.com/questions/6081436/how-to-use-alias-as-field-in-mysql/6081523#6081523 – Ravi Parekh Aug 31 '16 at 09:32

7 Answers7

72
SELECT salary INTO @variable1 FROM employee_info WHERE emp_id = 12345678 LIMIT 1;

or 

SET @variable1 = (SELECT salary FROM employee_info WHERE emp_id = 12345678 LIMIT 1);

SELECT @variable1;
Damith
  • 62,401
  • 13
  • 102
  • 153
  • 1
    For some reason this is returning a null, any idea why? – NateSHolland May 24 '12 at 16:48
  • 1
    what is the result you get when you run `SELECT salary FROM employee_info WHERE emp_id = 12345678`? – Damith May 24 '12 at 16:53
  • I was also getting null, my error was that I had used the same variable name as my field name, so `SET salary = (SELECT salary FROM.....` – Richard Tingle Jan 16 '15 at 11:35
  • @Damith, There is [actually 4 different syntax](http://stackoverflow.com/questions/15581005/set-a-variable-in-select-statement-mysql/29391880#29391880) to accomplish this thing. – Pacerier Apr 01 '15 at 20:10
  • 7
    The solutions are not exactly interchangeable: SELECT INTO variable1 will not change variable1 if the result of the query is an empty set, while SET variable1 = etc. will asign NULL to variable1 in that case. I think that this difference should be pointed out, because it can be meaningful in a lot of use cases. – pragmanomos May 21 '15 at 08:36
  • unable to get the result in variable..mysql showing error wrong syntax near `SELECT` – Rahul Sharma Dec 31 '16 at 09:56
13

You can even fill multiple variables in a single query.

SELECT salary, salary_group INTO @var1, @var2 FROM employee_info WHERE emp_id = 12345678;
Olias
  • 131
  • 1
  • 2
4

You are quite close to the right syntax. Here it is:

SET @variable1 = (SELECT salary FROM employee_info WHERE emp_id = 12345678);

and then print the variable like this:

SELECT @variable1;
Adam Fili
  • 463
  • 2
  • 9
2
SELECT @code:=salary FROM employee_info WHERE emp_id = 12345678;

To check salary,

SELECT @code;

The result of salary will be initialized in code.

More Information

Community
  • 1
  • 1
Ravi Parekh
  • 5,253
  • 9
  • 46
  • 58
2

Set the result of a query to a variable in MySQL 

Select  @Amount1:=  Amount FROM table where id=57703;
kavitha Reddy
  • 3,303
  • 24
  • 14
  • I used eveything here but it is not working for [my code](https://stackoverflow.com/questions/49196568/mysql-random-data-create-from-tables) – melic Mar 16 '18 at 15:01
1
select @variable1 := salary FROM employee_info WHERE emp_id = 12345678;
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 4
    This doesn't work dude. You need to [add a colon](http://stackoverflow.com/a/29391880/632951) for it to work. – Pacerier Apr 01 '15 at 13:17
  • 1
    change '=' to ':=' – Marcel Djaman Jul 04 '16 at 11:06
  • 2
    Although this code may be help to solve the problem, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Jul 04 '16 at 16:41
1

use this 

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

tested and workes fine...

Aman Maurya
  • 1,305
  • 12
  • 26