Table of Contents


Appendix B
DML Command Syntax And Examples

For those of you who aren’t familiar with the basic format of DML statements in Oracle, I’ve provided this reference. This reference is by no means a bible of every possible permutation of a statement, but it does cover each statement quite thoroughly. Chances are, the material presented here will be applicable in more than 99 percent of the DML statements you will write. Specifically, this appendix covers the DELETE, INSERT, SELECT, and UPDATE statements.

The DELETE Statement

The DELETE statement is used to remove one or more rows from a table. The basic format of a DELETE statement is as follows:

DELETE
FROM   <table>
WHERE  <one or more data conditions>;

The use of FROM is entirely optional, although the table name must be specified. The use of the WHERE clause is also optional, but excluding it will cause all the rows in the table to be deleted.

If you want to delete all rows in a table, you might consider using the TRUNCATE command instead. This command removes the rows in the table without generating rollback information. If you are absolutely positive that you don’t want the data, TRUNCATE is much faster than DELETE.

Be careful, though! Once you empty a table in this way, its contents are gone. If you later need the data, you will have to have your DBA recover the data from a backup.

The INSERT Statement

The INSERT statement is used to add a new row of data to a table. The basic format of an INSERT statement is as follows:

INSERT
INTO   <table>
       (<column listings>)
VALUES (<column values>);

If you are inserting column values in the order in which the table’s columns are defined, you may omit the column listing. Otherwise, you must include the column listings so Oracle will place the new data values in the proper columns.

It is possible to create multiple rows with a single INSERT statement. This is called a multiple insert. The format of a multiple insert is as follows:

INSERT
INTO   <destination table>
       <destination table column listings>
SELECT <source table column listings>
FROM   <source table>;

You may use an asterisk in place of the source table column listings to indicate that all of the source table’s columns should be selected. If this is the case, the layout of the source and destination tables must be exactly alike.

Multiple inserts are commonly used to copy data within a table back into the table with different primary key values. This is very useful when creating test data.

The SELECT Statement

The SELECT statement is used to retrieve data from a table. The basic format of a SELECT statement is as follows:

SELECT <column listing>
FROM   <source table> <source table alias>
WHERE  <one or more data conditions>;

If you wish to select all the columns that match your WHERE clause, you may use an asterisk instead of naming all the columns from your source table. The use of the WHERE clause is optional, but excluding the clause will cause all the rows of data to be retrieved from the table.

The use of a table alias is optional, but it is quite common in queries that join two or more tables to return data. The basic format when using a table alias is as follows:

SELECT <column listing>
FROM   <source table> <source table alias>,
       <source table> <source table alias>
WHERE  <one or more data conditions>;

A table alias is most often used to abbreviate a table’s name so that columns in the WHERE clause may be referenced with the abbreviation instead of the table name. When two columns in different tables share the same name, the desired column must be identified using either a table name or table alias.

The use of an optional ORDER BY clause is quite common, as well. The basic format of an ORDER BY clause is as follows:

SELECT <column listing>
FROM   <table> <table alias>
WHERE  <one or more data conditions>
ORDER BY <column>;

The ORDER BY clause instructs Oracle to gather the data and then sort it according to one or more conditions. Use of this clause adds overhead to the performance of the statement.

The UPDATE Statement

The UPDATE statement is used to modify data that already exists in a table. The basic format of an UPDATE statement is as follows:

UPDATE <table>
SET    <column> = <value>,
       <column> = <value>
WHERE  <one or more data conditions>;

You may specify as many columns as you like in the SET clause. The use of the WHERE clause is (once again) optional, but excluding the clause will cause every row in the table to be updated.


Table of Contents