Chapter 14 – Data Manipulation Language (DML)
“I tried to draw people more realistically, but the figure I neglected to update was myself.”
- Joe Sacco
INSERT Syntax # 1
The following syntax of the INSERT does not use the column names as part of the command. Therefore, it requires that the VALUES portion of the INSERT match each and every column in the table with a data value or a NULL.
INSERT[INTO] <table-name> VALUES
(<literal-data-value> [ ...,<literal-data-value> ] ;
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
CREATE TABLE SQL_CLASS.Employee_Table2
(Employee_No INTEGER not null,
Dept_No SMALLINT null,
Last_name CHAR(20) null,
First_name VARCHAR(12) null,
Salary NUMBER(8,2) null,
CONSTRAINT Employee2_pk PRIMARY KEY
(Employee_No));
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 is the literal representation for no data.
INSERT Syntax # 2
The syntax of the second type of INSERT follows:
INSERT [ INTO ] <table-name>
( <column-name> [...,<column-name> ]
VALUES
( <literal-data-value> [...,<literal-data-value> ] ;
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
INSERT INTO Employee_Table2
(Employee_No , First_Name, Last_Name, Salary)
VALUES( 2300123, 'Srini', 'Patel', 75000) ;
SELECT * FROM Employee_Table2
WHERE Employee_No = 2300123
The above 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/SELECT Command
The syntax of the INSERT / SELECT is:
INSERT [ INTO ] <table-name>
SELECT <column-name> [...,<column-name> ]
FROM <table-name> ;
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 Oracle. 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 (*)
When all columns are desired to make an exact copy of the second table and both tables have the exact same number of columns in the exact same order with the exact same data types; an * may be used in the SELECT to read all columns without a WHERE clause, as in the example below:
CREATE TABLE SQL_CLASS.Employee_Table4
(Employee_No INTEGER not null,
Dept_No SMALLINT null,
Last_name CHAR(20) null,
First_name VARCHAR(12) null,
Salary NUMBER(8,2) null,
CONSTRAINT Employee4_pk PRIMARY KEY
(Employee_No));
INSERT INTO Employee_Table4
SELECT *
FROM Employee_Table ;
Like all SELECT operations without a WHERE clause, a full table scan occurs and all the rows of the second table are inserted into the new Table, using only the data values from the columns listed.
INSERT/SELECT example with Less Columns
When fewer than all the columns are desired, the syntax below
will work. It will put NULL values in for the Salary column.
INSERT INTO Employee_Table2
(Employee_No
,Dept_No
,Last_Name
,First_Name)
SELECT Employee_No
,Dept_No
,Last_Name
,First_Name
FROM Employee_Table;
Select * from Employee_Table4
WHERE Employee_No = 1232578;
I In both of the above example, only the specified columns are populated in Employee_Table2 with NULL values for salary.
Two UPDATE Examples
UPDATE Employee_Table4
SET Last_Name = 'Singh'
,Dept_No = 500
,Salary = 75000
WHERE Employee_No = 1232578 ;
UPDATE Employee_Table4
SET Salary = 77000
WHERE Last_Name = 'Jones'
AND First_Name = 'Squiggy' ;
Select * from Employee_Table4
WHERE Employee_No IN (1232578, 2000000) ;
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
UPDATE <table-name>
[ FROM <table-name> [AS <alias-name> ] ]
SET <column-name> = { <expression-or-data-value> | <data-value> }
[..., <column-name> = <expression-or-data-value> | <data-value> ]
WHERE <column-name> [..., <column-name> ]
IN ( SELECT <column-name> [...,<column-name> ]
FROM <table-name>
[ WHERE <condition-test> ... ] ) ;
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
Employee_Table4 can be changed based on
Employee_Table. The following UPDATE uses a
subquery operation to accomplish the operation:
UPDATE Employee_Table4
SET Salary = 99000
WHERE Employee_No IN
(SELECT Employee_no
FROM Employee_Table
WHERE dept_no is null) ;
Select * from Employee_Table4
WHERE Employee_No = 2000000;
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
UPDATE <table-name>
SET <column-name> = { <expression-or-data-value> | <data-value> }
[..., <column-name> = <expression-or-data-value> | <data-value> ]
[ FROM <table-name> [ AS <alias-name> ] ]
WHERE [<table-name>.]<column-name> = [<table-name>.]<column-name>
[ AND <condition-test> ] [ OR <condition-test> ] ;
When adding an alias to the UPDATE, the alias becomes the table name and MUST be used in the WHERE clause when qualifying columns.
The DELETE Command Basic Syntax
DEL[ETE] [ FROM ] <table-name> [ AS <alias-name> ]
[ WHERE condition ] [ ALL ] ;
The DELETE statement has one function and that is to remove rows from a table. A status is the only returned value from the database. No rows are returned to the user. One of the fastest things that Teradata does is to remove ALL rows from a table.
The reason for its speed is that it simply moves all of the sectors allocated to the table onto the free sector list in the AMP’s Cylinder Index. It is the fast path and there is no OOPS command, unless the explicit transaction has not yet completed. In that case, a ROLLBACK statement can be issued to undo the delete operation before a COMMIT. Otherwise, the rows are gone and it will take either a backup tape or a BEFORE image in the Permanent Journal to perform a manual rollback. Be Very CAREFUL with DELETE. It can come back to bite you if you’re not careful.
DELETE ALL Rows in a Table
DELETE FROM Employee_Table4 ;
The above example deleted all rows from Employee_Table4.
A DELETE Example Deleting only Some of the Rows
DELETE FROM Employee_Table2 WHERE Employee_No < 1199999 ;
The DELETE example above only removes the rows that have an Employee_No less than 1199999.
Example of Subquery DELETE Command
DELETE FROM Employee_Table2
WHEREDept_No NOT IN
( SELECTDept_No FROM Department_Table ) ;
SELECT *
FROM Employee_Table2;
The above uses a Subquery and the DELETE command. Since there is no Dept_No = 10 in the Department_Table, Richard Smythe was deleted in the table Employee_Table2. One might think that Squiggy Jones should have been deleted also since there were no NULL values in the Dept_No column, but the NULL value did not delete.
Example of Subquery DELETE That Gets Rid of Null Values
DELETE FROM Employee_Table2
WHERE Employee_No IN
( SELECT Employee_No
FROM Employee_Table
WHERE Dept_No IS NULL ) ;
The above uses a Subquery and the DELETE command to get rid of NULL values. Squiggy Jones has now been deleted.