Chapter 18 – Data Manipulation Language (DML)

“Insanity: doing the same thing over and over again and expecting different results.”

- Albert Einstein

INSERT Syntax # 1

The INSERT statement is used to put new row(s) into a table. A status is the only returned value from the database. No rows are returned to the user. This INSERT syntax requires either a data value or a NULL for all the columns in a table. When executed, this code places a single new row into a table.

INSERT example with Syntax 1

After the execution of the above INSERT, there is a new row with the integer value of 1 going into Column1, the integer value of 5 going into Column2, the character value of Jones going into Column3, a NULL value going into Column4 , and an integer value of 45000 going into Column5. The NULL expressed in the VALUES list means no data.

INSERT Syntax # 2

The syntax of the second type of INSERT follows:

This is another form of the INSERT statement that can be used when some of the data is not available. It allows for the missing values (NULL) to be eliminated from the list in the VALUES clause. It is also the best format when the data is arranged in a different sequence than the CREATE TABLE.

INSERT example with Syntax 2

The statement INSERT statement incorporates both of the reasons to use this syntax. First, notice that the column names Last_Name and First_Name have been switched to match the data values. Also, notice that Dept_No does not appear in the column list, therefore it is assumed to be NULL. This is a good format to use when the data is coming from a file and does not match the order of the table columns.

INSERT example with Syntax 3

The third form of the INSERT statement can be used to insert numerous rows at the same time.

INSERT/SELECT Command

The syntax of the INSERT / SELECT is:

Although the INSERT is great for adding a single row not currently present in the system, an INSERT/SELECT is even better when the data already exists within Kognitio. In this case, the INSERT is combined with a SELECT. However, no rows are returned to the user. Instead, they go into the table as new rows.

The SELECT reads the data values from the one or more columns in one or more tables and uses them as the values to INSERT into another table. Simply put, the SELECT takes the place of the VALUES portion of the INSERT.

This is a common technique for building data marts, interim tables and temporary tables. It is normally a better and much faster alternative than extracting the rows to a data file, then reading the data file and inserting the rows using a utility.

INSERT/SELECT example using All Columns (*)

The rows of the second table are inserted into the new Table, using all columns in the table.

INSERT/SELECT example with Less Columns

In the above example, only the specified columns are populated in Employee_Table2. Salary will contain NULL values for each row inserted.

Two UPDATE Examples

The first UPDATE command modifies all rows for Employee_No 1232578. It changes the values in three columns with new data values provided after the equal sign (=). The next UPDATE uses the same table as the above statement. The UPDATE determines which row(s) to modify with compound conditions written in the WHERE clause based on values stored in other columns.

Subquery UPDATE Command Syntax

Sometimes it is necessary to update rows in a table when they match rows in another table. To accomplish this, the tables must have one or more columns in the same domain. The matching process then involves either a subquery or join processing.

Example of Subquery UPDATE Command

Sometimes it is necessary to update rows in a table when they match rows in another table. To accomplish this, the tables must have one or more columns in the same domain. The matching process then involves either a subquery or join processing.

Join UPDATE Command Syntax

When adding an alias to the UPDATE, the alias becomes the table name and MUST be used in the WHERE clause when qualifying columns.

Example of an UPDATE Join Command

Sometimes it is necessary to update rows in a table when they match rows in another table. To accomplish this, the tables must have one or more columns in the same domain. The matching process then involves either a subquery or join.

DELETE and TRUNCATE Examples

DELETE FROM  Employee_Table2  ;

TRUNCATE TABLE Employee_Table2  ;

The first example will only delete the rows that match the criteria. The second example will delete all the rows in the table. Use the TRUNCATE command to quickly remove all rows in a table.

To DELETE or to TRUNCATE

To DELETE or to TRUNCATE?

That is the question.

The answer is: TRUNCATE if you can.

DELETE FROM Employee_Table2  ;

TRUNCATE TABLE Employee_Table2  ;

TRUNCATING uses fewer system resources, and it is faster. So, TRUNCATE if you can.

Subquery and Join DELETE Command Syntax

The join syntax for the DELETE statement follows:

You may be asked to delete rows in one table based on data from a different table. Sometimes it is desirable to delete rows from one table based on their existence in or by matching a value stored in another table. To access these rows from another table for comparison, a subquery or a join operation can be used.

Example of Subquery DELETE Command

The above uses a Subquery and the DELETE command.