SET

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

SET @my_var = <expr> [, @my_var_b = <expr_b>, @my_var_c = ...]

Remarks

The expression can be as simple as a single digit or string, or it can be the result of a SQL statement. Basically, anything that is valid in the form: SELECT <expr> INTO @my_var;
The value assigned to the variable must be a single value (cannot assign multiple rows or columns).
User-defined variables are case-insensitive, which means @rollno is the same as @RollNo.
These variables are session-specific; they expire once the session ends. Also, a session variable defined by a user cannot be viewed or referenced by another session.
The data type of a user-defined variable is inferred automatically.
The INFORMATION_SCHEMA.USER_VARIABLES table contains a list of all the user-defined variables.

Examples

Setting the variable's value via a basic expression:

SQL

SET @a = 5 + 5;

SELECT @a;

+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.054 sec)

Setting the value referencing another user defined variable:

SQL

SET @a = 5 + 5;

SET @b = @a * @a;

SELECT @b;

+------+
| @b   |
+------+
|  100 |
+------+
1 row in set (0.051 sec)

Setting the value as the result of a SQL statement:

SQL

CREATE TABLE set_example (a int, b int);
INSERT INTO set_example VALUES(1,2),(3,4),(5,6),(7,8);

SELECT * FROM set_example;

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
+------+------+
4 rows in set (0.001 sec)

SQL

SET @a = (SELECT COUNT(*) FROM set_example);

SELECT @a;

+------+
| @a   |
+------+
|    4 |
+------+
1 row in set (0.000 sec)

SQL

SET @a = (SELECT b FROM set_example WHERE a = 7);

SELECT @a;

+------+
| @a   |
+------+
|    8 |
+------+
1 row in set (0.000 sec)

SQL

SET @a = (SELECT b,a FROM set_example WHERE a = 7);
ERROR 1241 (21000): Operand should contain 1 column(s)

SET @a = (SELECT b FROM set_example);
ERROR 1242 (21000): Subquery returns more than 1 row

User-defined variables on the right-hand side of assignments are bound before the SET statement is run.

SQL

SET @x = 1;

SET @y = 2;

SET @x = @y, @y = @x;

SELECT @x, @y;

+----+----+
| @x | @y |
+----+----+
| 2  | 1  |
+----+----+

Other than that, the assignments in the list are run left-to-right.

SQL

SET @x = 1, @x = 2;

SELECT @x;

+----+
| @x |
+----+
| 2  |
+----+

User defined variables and functions:

SQL

SET @a = 8;

DELIMITER //
USE db //

CREATE OR REPLACE FUNCTION foo (a INT) RETURNS INT AS BEGIN RETURN a * a; END //

DELIMITER ;

SET @c = foo(@a);

SELECT @a, @c;

+------+------+
| @a   | @c   |
+------+------+
|    8 |    64|
+------+------+

SQL

SET @d = (SELECT @a) * 2;
Query OK, 0 rows affected (0.098 sec)

SELECT @d;

+------+
| @d   |
+------+
|   16 |
+------+
1 row in set (0.055 sec)

SQL

SET @d = (SELECT @a) * 2, @e = foo(10), @f = @a + @b + @@SESSION.net_read_timeout;
Query OK, 0 rows affected (0.062 sec)

SELECT @d, @e, @f;

+------+------+------+
| @d   | @e   | @f   |
+------+------+------+
|   16 |  100 | 3708 |
+------+------+------+
1 row in set (0.055 sec)

SQL

SELECT @@SESSION.net_read_timeout;

+----------------------------+
| @@SESSION.net_read_timeout |
+----------------------------+
|                       3600 |
+----------------------------+
1 row in set (0.075 sec)

SQL

SET @g := @a + @b + @@SESSION.net_read_timeout;
Query OK, 0 rows affected (0.081 sec)

SELECT @g;

+------+
| @g   |
+------+
| 3708 |
+------+
1 row in set (0.069 sec)

Using strings and objects:

SQL

SET @t = FROM_UNIXTIME(1234567890), @s = 'abc', @fp = 3.14;

SELECT @t, @s, @fp;

+---------------------+-----+------+
| @t                  | @s  | @fp  |
+---------------------+-----+------+
| 2009-02-14 01:31:30 | abc | 3.14 |
+---------------------+-----+------+
1 row in set (0.053 sec)

SQL

SET @str = CONCAT(@s, @@GLOBAL.sql_mode);

SELECT @str;

+-----------------------------------------------------+
| @str                                                |
+-----------------------------------------------------+
| abcANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+-----------------------------------------------------+
1 row in set (0.059 sec)

SET

On this page