Data Manipulation Language (DML) Statement Reference

DML statements let you add, remove, and manage the data stored in tables. You can write and execute DML statements like any other SQL statements in the System. The SELECT SQL statement is sometimes considered a DML statement, and the Ocient System includes it in the Data Query Language (DQL). For details, see the Data Query Language (DQL) Statement Reference. Supported DML SQL statements are:

DELETE FROM TABLE
INSERT INTO TABLE
TRUNCATE TABLE

DELETE FROM TABLE

Removes rows from the specified table. You can use the WHERE clause to specify the rows to remove. If a DELETE SQL statement lacks the WHERE clause, then the database deletes all rows in the table. To use this statement, you must have the DELETE privilege for the table. For details and examples, see Remove Records from an Ocient System.

DELETE actions cannot be undone.If a DELETE operation fails during execution, the database rolls back the changes and returns to its original state.

Due to limitations of the JDBC API, the reported modified row count might not be accurate for DELETE operations that are larger than two billion rows.

Syntax

SQL

DELETE FROM table_name [ WITH cte ] [ WHERE <filter_clause> ]

Parameter	Type	Description
`table_name`	string	The name of the table, specified as a string, indicates where to delete rows.
`cte`	string	A common table expression that defines temporary data for the `DELETE` statement. For details about using common table expressions, see WITH.
`<filter_clause>`	None	A logical combination of predicates that filter the rows to delete based on one or more columns. For details, see the WHERE clause. The `DELETE` SQL statement removes all rows from a table if you do not include the `WHERE` clause.

Examples Delete Rows from the Table with Filter Criteria This DELETE SQL statement removes all rows in the movies table that have a budget of less than 10000.

SQL

DELETE FROM movies WHERE budget < 10000;

Delete Rows from the Table Using a Common Table Expression This example uses a common table expression using the WITH keyword to find rows representing all transactions that occurred before 2022 that are less than $100. The DELETE SQL statement receives the results from the common table expression. Then, the database executes this statement to delete the corresponding rows.

SQL

DELETE FROM transactions
    WITH old_transactions AS (
        SELECT
            transaction_id
        FROM
        	transactions
    	WHERE transaction_date < '2022-01-01'
        	AND amount < 100
	)
	WHERE transaction_id IN (
		SELECT transaction_id
		FROM old_transactions );

INSERT INTO TABLE

INSERT INTO inserts rows into a table in the current database using literal values, column references, function executions, computed expressions, or column default values. This SQL statement requires the INSERT privilege for the relevant table.

Due to limitations of the JDBC API, the reported modified row count might not be accurate for insert operations that are larger than two billion rows.

Syntax

SQL

INSERT INTO table_name [ ( col1, col2 [, ...] ) ]
    [ WITH cte ] { query | [ DEFAULT VALUES | VALUES [ <rows_to_insert> ] }

<rows_to_insert> ::=
    ( row1_exp1, row1_exp2 [, ...] ),
    ( row2_exp1, row2_exp2 [, ...] )
    [, ...]

Using DEFAULT VALUES inserts a single row where each target column is populated with its column defaults (as defined in the column definition) instead of an explicit VALUES list.For table columns that do not each have a defined default value, the inserted row is NULL. If the column has no default and also has the NOT NULL constraint, the insert operation generates an error.

Parameter	Type	Description
`table_name`	string	The name of the table for insertion.
`col1, col2 [, ... ]`	string	A list of specific columns to insert specific values. This column list defaults to all columns in the table if you do not specify any column names. The `INSERT` statement can use a subset of the table columns. Any columns not included in the statement are populated with their column default value in their column definition. If the column definition does not specify a default, the inserted row is NULL. If the column has no default and also has the `NOT NULL` constraint, the insert operation generates an error.
`cte`	string	A common table expression that defines temporary data for the `INSERT` statement. For details about using common table expressions, see WITH.
`query`	string	A `SELECT` query that defines values or a table and any of its columns that should be inserted into the specified `table_name`.
`row1_exp1, row1_exp2 [, ...]`	string	The expressions to insert into columns in the table. This list must match the number of columns specified in the INSERT statement. Similarly, each expression must match the data type of the column that corresponds to its position. Expressions can be any of the following: Literals: `1`, `3.14`, ‘`abc`’, `DATE '2024-01-01'`, etc. Column references: If your `INSERT` statement includes a common table expression using a `WITH` clause, you can reference columns from the separate table in that clause. Function executions: `ABS(-5)`, `NOW()`, `ST_DISTANCE`, etc. Computed expressions: `price * quantity`, `COALESCE(x, 0)`, etc. Column default values: Use the keyword `DEFAULT` to insert a default value specified in the column definition. If the column definition does not specify a default, the inserted row is NULL. If the column has no default and also has the `NOT NULL` constraint, the insert operation generates an error.

Examples Insert Values from One Column This example inserts the columns from system.table_b into system.table_a.

SQL

INSERT INTO system.table_a SELECT * FROM system.table_b;

Insert Values from Multiple Columns This example inserts the column system.table_b.id_col_b into system.table_a.id_col_a and system.table_b.int_col_b into system.table_a.int_col_a.

SQL

INSERT INTO system.table_a (id_col_a, int_col_a)
    SELECT id_col_b, int_col_b FROM system.table_b;

Insert Literal Values Create a table with product, quantity, and date of sale information with these non-nullable columns:

product — Product identifier
quantity — Quantity of the product sold
sale_date — Date of sale

SQL

CREATE TABLE sales (
    product INT NOT NULL,
    quantity INT NOT NULL,
    sale_date DATE NOT NULL
);

Insert three rows of literal values that represent different sales.

SQL

INSERT INTO sales (product, quantity, sale_date)
    VALUES
        (1, 10, '2023-01-15'),
        (2, 5, '2023-01-20'),
        (1, 8, '2023-02-05');

Insert Values Using a Common Table Expression In this example, a common table expression performs calculations on the sales table before inserting rows into the monthly_sales_summary table. The example uses the monthly_sales_summary table created by this CREATE TABLE statement with these non-nullable columns:

product_id — Product identifier
month — Month part of the date
total_quantity — Total quantity of the product

SQL

CREATE TABLE monthly_sales_summary (
    "product_id" INT NOT NULL,
    "month" DATE NOT NULL,
    "total_quantity" INT NOT NULL
);

The common table expression following the WITH keyword extracts the month from the sale date sale_date and calculates the sum of the quantity sold total_quantity of the product from the sales table before inserting this data. Then, the INSERT SQL statement specifies to insert the data into the monthly_sales_summary table.

SQL

INSERT INTO monthly_sales_summary (product_id, month, total_quantity)
WITH monthly_totals AS (
    SELECT
        product,
        DATE_TRUNC('month', sale_date) AS month,
        SUM(quantity_sold) AS total_quantity
    FROM
        sales
    GROUP BY
        product,
        DATE_TRUNC('month', sale_date)
)
SELECT
    product,
    month,
    total_quantity
FROM
    monthly_totals;

Insert Columns Using Default Values This code utilizes the customers table with these columns:

customer_id — Customer identifier
name — Customer name
status — Customer status with the default active status
created_at — Created date

SQL

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP
);

Use the DEFAULT VALUES keyword to insert one row of default values into the table. For columns that lack a defined default value, the operation inserts a NULL row.

SQL

INSERT INTO customers DEFAULT VALUES;

The resulting row contains all NULL values except for the status column, which has the active default value.

SQL

SELECT * FROM customers;

Output

SQL

   | customer_id | name  | status | created_at |
   | ----------- | ----- | ------ | ---------- |
   |             |       | ACTIVE |            |

Alternatively, you can insert default values by using the DEFAULT keyword as one of the row values in the INSERT statement.

SQL

INSERT INTO customers (customer_id, name, status, created_at)
    VALUES (1, 'Alice', DEFAULT, NULL);

Output

SQL

   | customer_id | name  | status | created_at |
   | ----------- | ----- | ------ | ---------- |
   |             |       | ACTIVE |            |
   | 1           | Alice | ACTIVE |            |

INSERT INTO TABLE USING LOADERS

Specify one or more Loader Nodes for executing the INSERT INTO SQL statement. If you do not use this option, the Ocient System uses all Loader Nodes that are live to execute the SQL statement. This statement is useful for managing loading operations, particularly when balancing multiple loads of different sizes and resource requirements. Alternatively, this statement can also help simplify small batch loads by sourcing the data from a single Loader Node. Syntax

SQL

INSERT INTO TABLE table_name USING LOADERS streamloader [, ... ]
     query

Parameter	Type	Description
`streamloader`	string	A unique name for the Loader Node. Identify the names of Loader Nodes from the `sys.nodes` table by using this query: `SELECT name FROM sys.nodes;` If the name of the streamloader contains special characters, you must enclose it in quotes, such as `"stream-loader1"`.

For the query to execute successfully, the specified names must:

Identify nodes that are live.
Identify nodes that have the Loader role.

Examples This example inserts the column system.table_b.id_col_b into system.table_a.id_col_a and system.table_b.int_col_b into system.table_a.int_col_a. Use the Loader Node named stream-loader1 to execute this SQL statement.

SQL

INSERT INTO system.table_a (id_col_a, int_col_a)
    USING LOADERS "stream-loader1"
    SELECT id_col_b, int_col_b FROM system.table_b;

In this example, execute the same SQL statement with two Loader Nodes named stream-loader2 and stream-loader3.

SQL

INSERT INTO system.table_a (id_col_a, int_col_a)
    USING LOADERS "stream-loader2","stream-loader3"
    SELECT id_col_b, int_col_b FROM system.table_b;

TRUNCATE TABLE

TRUNCATE TABLE removes some or all records from an existing table in the current database. The system deletes the truncated data, but the table and its schema remain intact in the system even if all data is deleted. If the entire table is truncated, Global Dictionary Compression tables remain in place. To truncate a table, you must have the DELETE privilege for the table. To remove a subset of rows from a table, you can use the DELETE FROM TABLE SQL statement. For details and examples of using TRUNCATE, see Remove Records from an Ocient System.

This action cannot be undone and results in data loss.

Syntax

SQL

TRUNCATE TABLE table_name

TRUNCATE TABLE table_name WHERE segment_group_id = <ID>

TRUNCATE TABLE table_name WHERE segment_group_id in (<ID>, ...)

Parameter	Type	Description
`table_name`	string	The name of the table to truncate.
`segment_group_id`	numeric	Identifier of the segment group.

Examples This example truncates an existing table in the current database and schema named students.

SQL

TRUNCATE TABLE students;

This example truncates an existing table in the current database named us.students.

SQL

TRUNCATE TABLE us.students;

This example truncates a single segment group from an existing table in the current database named students.

SQL

TRUNCATE TABLE students WHERE segment_group_id = 123456789;

This example truncates a number of segment groups from an existing table in the current database named us.students.

SQL

TRUNCATE TABLE us.students WHERE segment_group_id IN (1,2,3,4,5);

Data Query Language (DQL) Statement Reference Data Definition Language (DDL) Statement Reference Transactional Control Language (TCL) Statement Reference Commands Supported by the Ocient JDBC CLI Program Ocient Python Module (pyocient)

​DELETE FROM TABLE

​INSERT INTO TABLE

​INSERT INTO TABLE USING LOADERS

​TRUNCATE TABLE

​Related Links

DELETE FROM TABLE

INSERT INTO TABLE

INSERT INTO TABLE USING LOADERS

TRUNCATE TABLE

Related Links