SQL Bible, Second Edition

Chapter 6. Data Manipulation Language (DML)

Chapters 4 and 5 discussed an important part of SQL called the data definition language (DDL), which enables you to create, alter, and destroy various database objects. We emphasized the fact that tables are the most important database objects because they store data, and data is what databases are all about. This chapter deals with the data manipulation language (DML), which allows you to add data to the database, modify it as necessary, and destroy it when it is no longer needed.

DML consists of four statements: INSERT, UPDATE, DELETE, and MERGE.MERGE combines the functionality of the other three statements.

Note

Examples in this chapter assume incremental buildup of the ACME database started in Chapter 4, "Creating RDBMS Objects," and Chapter 5, "Altering and Destroying RDBMS Objects." Constraints implemented in the database scripts may prevent some of the statements from successful execution.

INSERT: Populating Tables with Data

As you know, tables in a relational database denote entities, or at least they should. For example, each row in the CUSTOMER table holds information about a specific customer, a row in ORDER_HEADER represents a definite order, and so on. Usually, the appearance of a new "real-life" entity calls for inserting a new row. For example, you would need a new row in the CUSTOMER table if ACME, Inc. obtained a new customer; you need to insert a row into the ORDER_HEADER table when a customer makes an order; a new row has ...