SET VAR
Description
The SET VAR command sets a temporary variable which has been previously declared in the current session.
To set a config variable or a hive variable use SET.
Syntax
SET { VAR | VARIABLE }
{ { variable_name = { expression | DEFAULT } } [, ...] |
( variable_name [, ...] ) = ( query ) }
Parameters
-
variable_name
Specifies an existing variable. If you specify multiple variables, there must not be any duplicates.
-
expression
Any expression, including scalar subqueries.
-
DEFAULT
If you specify
DEFAULT, the default expression of the variable is assigned, orNULLif there is none. -
query
A query that returns at most one row and as many columns as the number of specified variables. Each column must be implicitly castable to the data type of the corresponding variable. If the query returns no row
NULLvalues are assigned.
Examples
--
DECLARE VARIABLE var1 INT DEFAULT 7;
DECLARE VARIABLE var2 STRING;
-- A simple assignment
SET VAR var1 = 5;
SELECT var1;
5
-- A complex expression assignment
SET VARIABLE var1 = (SELECT max(c1) FROM VALUES(1), (2) AS t(c1));
SELECT var1;
2
-- resetting the variable to DEFAULT
SET VAR var1 = DEFAULT;
SELECT var1;
7
-- A multi variable assignment
SET VAR (var1, var2) = (SELECT max(c1), CAST(min(c1) AS STRING) FROM VALUES(1), (2) AS t(c1));
SELECT var1, var2;
2 1
-- Too many rows
SET VAR (var1, var2) = (SELECT c1, CAST(c1 AS STRING) FROM VALUES(1), (2) AS t(c1));
[ROW_SUBQUERY_TOO_MANY_ROWS] More than one row returned by a subquery used as a row. SQLSTATE: 21000
-- No rows
SET VAR (var1, var2) = (SELECT c1, CAST(c1 AS STRING) FROM VALUES(1), (2) AS t(c1) WHERE 1=0);
SELECT var1, var2;
NULL NULL