Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

DML Command Syntax and Examples
Oracle Tips by Burleson
 

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.

                
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter