Skip to main content
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> ]
ParameterTypeDescription
table_namestringThe name of the table, specified as a string, indicates where to delete rows.
ctestringA common table expression that defines temporary data for the DELETE statement.
For details about using common table expressions, see WITH.
<filter_clause>NoneA 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.
ParameterTypeDescription
table_namestringThe name of the table for insertion.
col1, col2 [, ... ]stringA 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.
ctestringA common table expression that defines temporary data for the INSERT statement.
For details about using common table expressions, see WITH.
querystringA 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 [, ...]stringThe 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:

L****iterals: 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
ParameterTypeDescription
streamloaderstringA 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>, ...)
ParameterTypeDescription
table_namestringThe name of the table to truncate.
segment_group_idnumericIdentifier 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)
Last modified on June 24, 2026