docs/_docs/sql-reference/dml.adoc

// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Data Manipulation Language (DML)

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


== DELETE

Deletes data from a table.

[source,sql]
----
DELETE FROM tablePrimary [ [ AS ] alias ]
[ WHERE booleanExpression ]
----

== INSERT

Inserts data into a table.

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

=== JOINs

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


== MERGE

Merge data into a table.


[source,sql]
----
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.

[discrete]
=== Examples

Merge some `NewPersons` into the `Person` table:


[source,sql]
----
MERGE INTO Person USING NewPersons
----

== SELECT

Retrieves data from a table or multiple tables.

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

== UPDATE

Updates data in a table.

[source,sql]
----
UPDATE tablePrimary
SET assign [, assign ]*
[ WHERE booleanExpression ]
----

== WITH

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


[source,sql]
----
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`.

[discrete]
=== Example


[source,sql]
----
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;
----