Source: https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/variable-definition/table-variable/
Documentation version: v5.3.2

# Table variables

Table variables store data in tabular form. In NebulaGraph Database Developer Edition, they are mainly used to store query results, intermediate traversal results, and to drive batch insertion, update, or deletion of nodes and edges.

The sections below cover local table variables in NebulaGraph Database Developer Edition.

## Syntax

diagramsource

 table_variable_definition table_value_constructor table_expression  

````
table_variable_definition ::=
    'BINDING'? 'TABLE' table_variable ( 
        ((( '::' | 'TYPED' ) 'BINDING TABLE')?  
            '{' (field_name value_type) (',' (field_name value_type))* '}' ('=' table_value_constructor)?
        )  
        | '{' field_name (',' field_name)* '}' '=' table_value_constructor
        | '=' table_value_constructor
    ) 

table_value_constructor ::=
    table_expression
    | '{' procedure_body '}' 

table_expression ::=
    ( '{' (field_name ':' value_expression) (',' (field_name ':' value_expression))* '}' ) (',' ( '{' (field_name ':' value_expression) (',' (field_name ':' value_expression))* '}' ) )* 
    | ( '(' value_expression (',' value_expression)* ')' ) ( ',' ( '(' value_expression (',' value_expression)* ')' ) )*                

````

## Parameters

### `table_variable`

The name of the table variable to be defined. `table_variable` can be a regular identifier or a delimited identifier enclosed with backticks (```). For more information about identifiers, see [Rules for naming identifiers](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/overview/keywords-identifiers/).

### `field_name`

The name of a field in the table. `field_name` must be an identifier. For more information about identifiers, see [Rules for naming identifiers](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/overview/keywords-identifiers/).

### `value_type`

The data type of the value that the field holds. Supported data types include numeric, string, boolean, temporal, vector, list, set, map, and record. For more information, see [Data types](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-types/).

### `value_expression`

The value of the field in the table. `value_expression` must be an expression that does not depend on any variables. For more information about value expressions, see [Value expressions](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/fe/#value_expressions).

### `procedure_body`

One or a set of DQL statements to be executed. For more information, see [Procedure body](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/procedures/create/#procedure_body) and [DQL statements](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/dql/).

## Member functions

### clear()

Removes all data from the table variable.

#### Function signature

````
clear() -> VOID

````

#### Usage notes

You can call `clear()` only through the `SET` statement. For more information, see [SET statement](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/set/).

## Examples

The following examples are based on the [example dataset](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/overview/example-dataset/).

### Define a table variable without assigning values

````
'''
TABLE t :: TABLE { a INT, 
                   b BOOL, 
                   c TYPED STRING, 
                   d DOUBLE, 
                   e LIST<DECIMAL(10, 4)>, 
                   f RECORD {x INT, y STRING}, 
                   g DATE, 
                   h VECTOR<3,FLOAT> 
   }  
   FINISH
'''

````

### Define a table variable with typed fields

````
'''
TABLE t TYPED TABLE { id INT64, name STRING, birthDate DATE } =  
    { id: 10001, name: 'Alice', birthDate: DATE '1990-01-01' },  
    { id: 10002, name: 'Bob', birthDate: DATE '1991-02-02' },  
    { id: 10003, name: 'Charlie', birthDate: DATE '1992-03-03' }  
FOR re IN t  
RETURN  re.id, re.name, re.birthDate 
'''

````

The preceding statement defines a table variable `t` with fields `id`, `name`, and `birthDate`, explicitly specifies the data type of each field, and adds three rows of data to the table. The `FOR` clause iterates over the table variable `t` and returns the values of the fields `id`, `name`, and `birthDate`.

Return:

````
+-------+---------+--------------+
| re.id | re.name | re.birthDate |
+-------+---------+--------------+
| 10001 | Alice   | 1990-01-01   |
| 10002 | Bob     | 1991-02-02   |
| 10003 | Charlie | 1992-03-03   |
+-------+---------+--------------+

````

### Define a table variable with untyped fields

````
'''
TABLE t { id, name, birthDate } =  
    ( 10001, 'Alice', DATE '1990-01-01' ),  
    ( 10002, 'Bob', DATE '1991-02-02' ),  
    ( 10003, 'Charlie', DATE '1992-03-03' )  
FOR re IN t  
RETURN  re.id, re.name, re.birthDate 
'''

````

The preceding statement defines a table variable `t` with fields `id`, `name`, and `birthDate`, and specifies the data type of each field implicitly when adding three rows of data to the table. The `FOR` clause iterates over the table variable `t` and returns the values of the fields `id`, `name`, and `birthDate`.

Return:

````
+-------+---------+--------------+
| re.id | re.name | re.birthDate |
+-------+---------+--------------+
| 10001 | Alice   | 1990-01-01   |
| 10002 | Bob     | 1991-02-02   |
| 10003 | Charlie | 1992-03-03   |
+-------+---------+--------------+

````

### Define a table variable from a query result

#### Infer field names and types from the query

````
'''
TABLE t = { USE movie 
            MATCH (v:Person) 
            RETURN v.name AS name, v.id AS id 
            ORDER BY name 
            LIMIT 3 
          } 
FOR rec IN t                                
RETURN rec.name, rec.id
'''

````

Return:

````
+------------------+--------+
| rec.name         | rec.id |
+------------------+--------+
| Aaron Eckhart    | 6383   |
| Ada Lonati       | 162562 |
| Adam Alexi-Malle | 20804  |
+------------------+--------+

````

The preceding statement defines a table variable `t` from the result of a `MATCH` statement. The field names (`name`, `id`) and their types are inferred from the `RETURN` clause aliases.

#### Specify explicit field names

````
'''
TABLE t { personName, personId } = {
    USE movie
    MATCH (v:Person)
    RETURN v.name, v.id
    ORDER BY v.name
    LIMIT 3
}
FOR rec IN t
RETURN rec.personName, rec.personId
'''

````

Return:

````
+------------------+--------------+
| rec.personName   | rec.personId |
+------------------+--------------+
| Aaron Eckhart    | 6383         |
| Ada Lonati       | 162562       |
| Adam Alexi-Malle | 20804        |
+------------------+--------------+

````

The preceding statement defines a table variable `t` with explicit field names `personName` and `personId`. The query returns `v.name` and `v.id`, which are mapped to the specified field names in order.

#### Specify explicit field names and types

````
'''
TABLE t TYPED TABLE { name STRING, id INT64 } = {
    USE movie
    MATCH (v:Person)
    RETURN v.name, v.id 
    ORDER BY v.name
    LIMIT 3
}
FOR rec IN t
RETURN rec.name, rec.id
'''

````

Return:

````
+------------------+--------+
| rec.name         | rec.id |
+------------------+--------+
| Aaron Eckhart    | 6383   |
| Ada Lonati       | 162562 |
| Adam Alexi-Malle | 20804  |
+------------------+--------+

````

The preceding statement defines a table variable `t` with types. The query output must be compatible with the declared types; otherwise, an error is raised.

#### Use correlated variables in a query

````
'''
VALUE personId = 100
TABLE friends = {
    USE movie
    MATCH (f:Person)-[:Act]->(m:Movie)
    WHERE f.id = personId
    RETURN f.id AS fid, f.name AS fname
}
FOR rec IN friends
RETURN rec.fid, rec.fname
'''

````

Return:

````
+---------+----------------+
| rec.fid | rec.fname      |
+---------+----------------+
| 100     | Scott Speedman |
+---------+----------------+

````

### Remove data from a table variable

````
'''
TABLE t {id, name} = (10, "new1"), (20, "new2")
SET t.clear()
FOR rec IN t
RETURN rec.id, rec.name
'''

````

Return:

````
+--------+----------+
| rec.id | rec.name |
+--------+----------+
+--------+----------+

````

### Batch insert nodes with a table variable

````
'''
TABLE t { id, name, birthDate } =  
    ( 10001, 'Alice', DATE '1990-01-01' ),  
    ( 10002, 'Bob', DATE '1991-02-02' ),  
    ( 10003, 'Charlie', DATE '1992-03-03' )  
USE movie 
FOR re IN t 
INSERT (@Actor {id: re.id, name: re.name, birthDate: re.birthDate})
'''  

````

The preceding statement defines a table variable `t` and iterates over the table variable to insert data into the graph `movie` as nodes of the node type `Actor`.

For more information about insert operations, see [INSERT](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/dml/insert/).

### Batch insert nodes and edges with a table variable

````
'''
TABLE t { actor_id, movie_id, movie_name } =  
    ( 10001 , 20001, 'Movie1' ),  
    ( 10002 , 20002, 'Movie2' ),  
    ( 10003 , 20003, 'Movie3' )  
USE movie 
FOR re IN t 
MATCH (a@Actor) WHERE a.id = re.actor_id 
INSERT (a)-[@Act]->(@Movie {id: re.movie_id, name: re.movie_name})
'''

````

The preceding statement defines a table variable `t` and iterates over the table variable to insert data into the graph `movie`. The statement matches nodes of the node type `Actor` based on the field `actor_id` in the table variable and inserts nodes of the node type `Movie` based on fields `movie_id` and `movie_name` in the table variable and also inserts edges between the nodes.

### Batch update nodes with a table variable

````
'''
TABLE t { id, new_name } =  
    ( 10001, 'Alice_renamed' ),  
    ( 10002, 'Bob_renamed' ),  
    ( 10003, 'Charlie_renamed' )  
USE movie 
FOR re IN t 
MATCH (a@Actor) WHERE a.id = re.id 
SET a.name = re.new_name
'''

````

The preceding statement defines a table variable `t` and matches nodes of the node type `Actor` based on the field `id` in the table variable. It then updates the property `name` of the matched nodes based on the field `new_name` in the table variable.

For more information about update operations, see [SET](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/dml/set/).

### Batch delete nodes with a table variable

````
'''
TABLE t { id } =  
    ( 10001 ),  
    ( 10002 ),  
    ( 10003 )  
USE movie 
FOR re IN t 
MATCH (a@Actor) WHERE a.id = re.id 
DETACH DELETE a
'''

````

The preceding statement defines a table variable `t` and matches nodes of the node type `Actor` based on the field `id` in the table variable. It then deletes the matched nodes and all their connecting edges.

For more information about delete operations, see [DELETE](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/dml/delete/).

### Use a table variable in a temporary graph

The following example is based on the temporary graph `#movie_temporary_graph` created in [CREATE TEMPORARY GRAPH](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/ddl/graph-projection/create/).

````
'''
USE #movie_temporary_graph {
    TABLE result_table TYPED TABLE { id INT, name STRING }

    MATCH (a@Movie)
    PER NODE (a) {
        EXPORT a.id AS id, a.name AS name INTO result_table
    }

    FOR r IN result_table
    RETURN r.id AS id, r.name AS name
    LIMIT 3
}
'''

````

Return:

````
+-----+------------+
| id  | name       |
+-----+------------+
| 572 | Next Door  |
| 427 | My Uncle   |
| 269 | Breathless |
+-----+------------+

````

The preceding statement defines a table variable `result_table` in the existing temporary graph `#movie_temporary_graph`. The table variable is populated by a match compute statement that exports the `id` and `name` fields of `Movie` nodes into `result_table`. Finally, the `FOR` clause iterates over `result_table` and returns three records.