Data Manipulation Language (DML)

This section walks you through all data manipulation language (DML) commands supported by GridGain 9.0.

DELETE

Deletes data from a table.

DELETE FROM tablePrimary [ [ AS ] alias ]
[ WHERE booleanExpression ]

INSERT

Inserts data into a table.

{ INSERT } INTO tablePrimary
[ '(' column [, column ]* ')' ]
query

JOINs

GridGain supports colocated and non-colocated distributed SQL joins. Furthermore, if the data resides in different tables, GridGain allows for cross-table joins as well.

MERGE

Merge data into a table.

MERGE INTO tablePrimary [ [ AS ] alias ]
USING tablePrimary
ON booleanExpression
[ WHEN MATCHED THEN UPDATE SET assign [, assign ]* ]
[ WHEN NOT MATCHED THEN INSERT VALUES '(' value [ , value ]* ')' ]

Parameters

tableName - the name of the table to be updated.
columnName - the name of a column to be initialized with a value from a VALUES clause.

Description

MERGE updates existing entries and inserts new entries.

Examples

Merge some NewPersons into the Person table:

MERGE INTO Person USING NewPersons

SELECT

Retrieves data from a table or multiple tables.

SELECT [ hintComment ] [ ALL | DISTINCT ]
    { * | projectItem [, projectItem ]* }
FROM tableExpression
[ WHERE booleanExpression ]
[ GROUP BY { groupItem [, groupItem ]* } ]
[ HAVING booleanExpression ]

UPDATE

Updates data in a table.

UPDATE tablePrimary
SET assign [, assign ]*
[ WHERE booleanExpression ]

WITH

Used to name a sub-query, can be referenced in other parts of the SQL statement.

WITH  { query_name [( columnName [,...] )] AS ( query ) [,...] }

Parameters

query_name - the name of the sub-query to be created. The name assigned to the sub-query is treated as though it was an inline view or table.

Description

WITH creates a sub-query. One or more common table entries can be referred to by name. Column name declarations are optional - the column names will be inferred from the named select queries. The final action in a WITH statement can be a select, insert, update, merge, delete, or create table.

Example

WITH cte1 AS (
        SELECT 1 AS FIRST_COLUMN
), cte2 AS (
        SELECT FIRST_COLUMN+1 AS FIRST_COLUMN FROM cte1
)
SELECT sum(FIRST_COLUMN) FROM cte2;

© 2025 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Jan 09, 2025