Assign Value to MySQL User Variable from Multiple Rows

In case, if we will assign a value to a user variable using a statement that returns multiple rows then the value from the last row would be saved in that user variable because user variables can save the only single value. Following the example, in which we are using data from table ‘Tender’, will exhibit it −

Example

mysql> select * from Tender;
+----+---------------+--------------+
| Sr | CompanyName   | Tender_value |
+----+---------------+--------------+
| 1  | Abc Corp.     |   250.369003 |
| 2  | Khaitan Corp. |   265.588989 |
| 3  | Singla group. |   220.255997 |
| 4  | Hero group.   |   221.253006 |
| 5  | Honda group   |   225.292266 |
+----+---------------+--------------+
5 rows in set (0.04 sec)

Above result set shows the data from table ‘Tender’. Now we will assign the values in

column ‘companyname’ in the variable @name as follows −

mysql> Select @name := companyname from tender;
+----------------------+
| @name := companyname |
+----------------------+
| Abc Corp.            |
| Khaitan Corp.        |
| Singla group.        |
| Hero group.          |
| Honda group          |
+----------------------+
5 rows in set (0.00 sec)

But, now when we refer this variable, it gives only the name of the company which was at last row. It is because of user variable can store only single value.

mysql> Select @name;
+-------------+
| @name       |
+-------------+
| Honda group |
+-------------+
1 row in set (0.00 sec)