DML Commands

You are looking at an older version of the documentation. The latest version is found here.

The Data Virtuality Server supports SQL for issuing queries and defining view transformations. Nearly all these features follow standard SQL syntax and functionality, so you can use any SQL reference for more information.

There are four basic commands for manipulating data in SQL, corresponding to the CRUD (CREATE, READ, UPDATE, and DELETE) operations: INSERT, SELECT, UPDATE, and DELETE. In addition, procedures can be executed using the EXECUTE command, via a procedural relational command, or in an anonymous procedure block.

SELECT Command

The SELECT command is used to retrieve records for any number of relations.

A SELECT command has several clauses:

WITH ...

SELECT ...

INTO ...

[ FROM ... ]

[ WHERE ... ]

[ GROUP BY ... ]

[ HAVING ... ]

[ ORDER BY ... ]

[ (LIMIT ...) | ( OFFSET ... [FETCH ...])]

[ OPTION ... ]

All of these clauses except OPTION are defined by the SQL specification. The specification also specifies the order that these clauses will be logically processed. Below is the processing order where each stage passes a set of rows to the following stage. Note that this processing model is logical and does not represent how any actual database engine performs the processing. However, it is a useful model for understanding questions about SQL.

Stage	Description
`WITH`	Gathers all rows from all with items in the order listed. Subsequent with items and the main query can reference the `WITH` item as if it is a table
`FROM`	Gathers all rows from all tables involved in the query and logically joins them with a Cartesian product, producing a single large table with all columns from all tables. Joins and join criteria are then applied to filter rows that do not match the join structure
`WHERE`	Applies a criterion to every output row from the FROM stage, further reducing the number of rows
`GROUP BY`	Groups sets of rows with matching values in the group by columns
`HAVING`	Applies criteria to each group of rows. Criteria can only be applied to columns that will have constant values within a group (those in the grouping columns or aggregate functions applied across the group)
`SELECT`	Specifies the column expressions that should be returned from the query. Expressions are evaluated, including aggregate functions based on the groups of rows, which will no longer exist after this point. The output columns are named using either column aliases or an implicit name determined by the engine. If `SELECT DISTINCT` is specified, duplicate removal will be performed on the rows being returned from the `SELECT` stage
`ORDER BY`	Sorts the rows returned from the `SELECT` stage as desired. Supports sorting on multiple columns in the specified order, ascending or descending. The output columns will be identical to those columns returned from the `SELECT` stage and will have the same name
`LIMIT`	Returns only the specified rows (with skip and limit values)
`INTO`	Logically applied last in processing

This model can be used to understand many questions about SQL. For example, columns aliased in the SELECT clause can only be referenced by alias in the ORDER BY clause. Without knowledge of the processing model, this can be somewhat confusing. In light of the model, it is clear that the ORDER BY stage is the only stage occurring after the SELECT stage, which is where the columns are named. Because the WHERE clause is processed before the SELECT, the columns have not yet been named, and the aliases are not yet known.

VALUES Command

The VALUES command is used to construct a simple table.

Syntax:

SQL

VALUES (value,...)

SQL

VALUES (value,...), (valueX,...) ...

A VALUES command with a single value set is equivalent to SELECT value, ….. A VALUES command with multiple values sets is equivalent to a UNION ALL of simple SELECTs: SELECT value, …. UNION ALL SELECT valueX, ….

INSERT Command

The INSERT command is used to add a record to an existing table. The target table is specified using an INTO clause.

Syntax:

CODE

INSERT INTO table (column,...) VALUES (value,...)
-- or
INSERT INTO table (column,...) SELECT * FROM x

UPDATE Command

The UPDATE command is used to modify records in a table. The operation may result in 1 or more records being updated or in no records being updated if none match the criteria.

Syntax:

SQL

UPDATE table SET (column=value,...) [WHERE criteria]

DELETE Command

The DELETE command is used to remove records from a table. The operation may result in 1 or more records being deleted or in no records being deleted if none match the criteria.

Syntax:

SQL

DELETE FROM table [WHERE criteria]

Rowcount

INSERT, SELECT INTO, UPDATE, and DELETE commands can report an integer row modification count: rowcount. If a large number of rows is modified, then the max integer value will be reported (2^31 -1).

SQL

BEGIN UPDATE table SET column = value WHERE criteria; SELECT rowcount; END;

EXECUTE Command

The EXECUTE command is used to execute a procedure, such as a virtual or stored procedure. Procedures may have zero or more scalar input parameters. The return value from a procedure is a result set, the same as is returned from a SELECT. Note that EXEC or CALL can be used as a short form of this command.

Syntax:

SQL

EXECUTE proc()
-- or
EXEC proc()
-- or
CALL proc()
-- or
EXECUTE proc(value, ...)

Named parameter syntax:

SQL

EXECUTE proc(name1=>value1,name4=>param4, ...)

Syntax Rules

The default order of parameter specification is the same as how they are defined in the procedure definition;
You can specify the parameters in any order by name. Parameters that have default values and/or are nullable in the metadata can be omitted from the named parameter call and will have the appropriate value passed at runtime;
If the procedure does not return a result set, the values from the RETURN, OUT, and IN_OUT parameters will be returned as a single row when used as an inline view query;
A VARIADIC parameter may be repeated 0 or more times as the last positional argument.

Procedural Relational Command

Please see Relational Syntax for Calling Virtual Procedures.

Anonymous Procedure Block

A procedure language block may be executed as a user command. This is advantageous when a virtual procedure does not exist, but a set of processing can be cared out on the server side together.

Syntax:

SQL

BEGIN 
	UPDATE table SET column = value WHERE criteria; SELECT rowcount; 
END;

Syntax Rules

IN, OUT and RETURN parameters are not supported;

A single result will be returned if any statement returns a result set. All returnable result sets must have a matching number of columns and types. Use the WITHOUT RETURN clause to indicate that a statement is not intended to a result set as needed.

EXPLAIN Command

The EXPLAIN command is used to analyze your queries to detect performance issues and the lineage of the objects used in a query. When you precede a SELECT statement with the keyword EXPLAIN, the Data Virtuality Server displays information from the optimizer about the statement execution and data lineage plans. That is, the Data Virtuality Server explains how it would process the statement, including information about the origin of the columns used in the query, how tables and views are joined and in which order, etc.

Syntax:

SQL

EXPLAIN SELECT * FROM table; 
-- or
EXPLAIN DELETE FROM table [WHERE criteria];