> ## Documentation Index > Fetch the complete documentation index at: https://docs.ocient.com/llms.txt > Use this file to discover all available pages before exploring further. # Data Manipulation Language (DML) Statement Reference export const Ocient = "Ocient®"; 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 {Ocient} 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](/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](/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 SQL theme={null} DELETE FROM table_name [ WITH cte ] [ WHERE ] ``` | **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](/data-query-language-dql-statement-reference#with). | | `` | None | A logical combination of predicates that filter the rows to delete based on one or more columns.
For details, see the [WHERE](/data-query-language-dql-statement-reference#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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} INSERT INTO table_name [ ( col1, col2 [, ...] ) ] [ WITH cte ] { query | [ DEFAULT VALUES | VALUES [ ] } ::= ( 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](/data-query-language-dql-statement-reference). | | `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:

**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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} INSERT INTO customers DEFAULT VALUES; ``` The resulting row contains all NULL values except for the `status` column, which has the `active` default value. ```sql SQL theme={null} SELECT * FROM customers; ``` Output ```sql SQL theme={null} | 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 SQL theme={null} INSERT INTO customers (customer_id, name, status, created_at) VALUES (1, 'Alice', DEFAULT, NULL); ``` Output ```sql SQL theme={null} | 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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} 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](#delete-from-table) SQL statement. For details and examples of using `TRUNCATE`, see [Remove Records from an Ocient System](/remove-records-from-an-ocient-system). This action cannot be undone and results in data loss. **Syntax** ```sql SQL theme={null} TRUNCATE TABLE table_name TRUNCATE TABLE table_name WHERE segment_group_id = TRUNCATE TABLE table_name WHERE segment_group_id in (, ...) ``` | **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 SQL theme={null} TRUNCATE TABLE students; ``` This example truncates an existing table in the current database named `us.students`. ```sql SQL theme={null} TRUNCATE TABLE us.students; ``` This example truncates a single segment group from an existing table in the current database named `students`. ```sql SQL theme={null} 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 SQL theme={null} TRUNCATE TABLE us.students WHERE segment_group_id IN (1,2,3,4,5); ``` ## Related Links [Data Query Language (DQL) Statement Reference](/data-query-language-dql-statement-reference) [Data Definition Language (DDL) Statement Reference](/data-definition-language-ddl-statement-reference) [Transactional Control Language (TCL) Statement Reference](/transaction-control-language-tcl-statement-reference) [Commands Supported by the Ocient JDBC CLI Program](/commands-supported-by-the-ocient-jdbc-cli-program) [Ocient Python Module (pyocient)](/ocient-python-module-pyocient)