SingleStore DB

Examples
Example 1

The following example assigns a value to a user-defined variable, and then uses it in a SELECT statement.

SELECT 3.14 INTO @pi;

SELECT Radius, Radius*POW(@pi,2) AS "Area" FROM circle;
****
+--------+---------+
| Radius | Area    |
+--------+---------+
|      2 | 19.7192 |
|      3 | 29.5788 |
+--------+---------+

The data type of the user-defined variable was inferred automatically, based on its value.

SELECT * FROM INFORMATION_SCHEMA.USER_VARIABLES;
****
+---------------+----------------+-----------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE         |
+---------------+----------------+-----------------------+
| pi            | 3.14           | decimal(3,2) NOT NULL |
+---------------+----------------+-----------------------+
Example 2

The following example assigns a value to a user-defined variable using a SELECT ... INTO statement.

SELECT * FROM courses ORDER BY course_code, section_number;
****
+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101      |              1 |              20 |
| CS-101      |              2 |              16 |
| CS-101      |              3 |              22 |
| CS-101      |              4 |              25 |
| CS-101      |              5 |              22 |
| CS-150      |              1 |              10 |
| CS-150      |              2 |              16 |
| CS-150      |              3 |              11 |
| CS-150      |              4 |              17 |
| CS-150      |              5 |               9 |
| CS-201      |              1 |              14 |
| CS-201      |              2 |              17 |
| CS-301      |              1 |               7 |
| CS-301      |              2 |              10 |
+-------------+----------------+-----------------+
SELECT number_students FROM courses WHERE course_code = 'CS-301' and section_number = 1 INTO @ns;

SELECT @ns;
****
+------+
| @ns  |
+------+
|    7 |
+------+

Note: The SELECT ... INTO statement must return only a single value.

Example 3

The following example shows how using @ in a LOAD DATA query is interpreted as a reference to LOAD DATA assignment, instead of a user-defined variable.

SELECT 100 INTO @Views;

SELECT @Views;
****
+--------+
| @Views |
+--------+
|    100 |
+--------+
SELECT * FROM allviews;
****
+-------+---------+-------+
| State | Product | Views |
+-------+---------+-------+
| NY    | C       |    15 |
| CA    | D       |    20 |
| CA    | A       |    60 |
| CA    | E       |    50 |
| CA    | G       |    10 |
| NY    | B       |    20 |
| NY    | F       |    40 |
+-------+---------+-------+
cat /tmp/data.csv
****
TX,H,25
TX,I,10
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE allviews
FIELDS TERMINATED BY ','
(State,Product,@Views)
SET Views = @Views + 10;

SELECT * FROM allviews;
****
+-------+---------+-------+
| State | Product | Views |
+-------+---------+-------+
| NY    | C       |    15 |
| CA    | D       |    20 |
| TX    | H       |    35 |
| TX    | I       |    20 |
| CA    | A       |    60 |
| CA    | E       |    50 |
| CA    | G       |    10 |
| NY    | B       |    20 |
| NY    | F       |    40 |
+-------+---------+-------+