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

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