Summary

The three classical DML statements are INSERT, UPDATE, and DELETE.

RDBMS data is dynamic by definition because it represents real-world entities that often change. When a new entity emerges that is relevant to your database, you create new row(s) of data in one or more tables that represent this entity using an INSERT statement. When an entity already exists in the database changes in real world, you modify database information about this entity using an UPDATE statement. When an entity is no longer relevant to your database or disappears from the real world completely, you remove information about it from your database using a DELETE statement.

DML statements have different granularity. The smallest unit you can INSERT or DELETE is one row; UPDATE can perform changes on a singe column of a single row. Even though you might say you are deleting values from a column or inserting values into a column, you would actually use an UPDATE statement to set the column values to nulls or to certain values, correspondingly.

It is critical to understand the importance of the WHERE clause of UPDATE and DELETE statements. When the WHERE clause is omitted, all target table rows are affected (modified or removed).

Some vendors have additional statements that perform similar functions to the classical DML statements. In this chapter, we described Oracle 9i's MERGE statement that combines the functionality of INSERT and UPDATE statements. The TRUNCATE statement supported by Oracle and MS SQL Server was also discussed; it works in a way like the DELETE statement without a WHERE clause.