DML for Making Changes

DML stands for Data Manipulation Language. DML commands are the SQL statements that can change the values in database tables, as opposed to merely reading them, as SELECT statements do.

DML (Data Manipulation Language)

Includes INSERT, UPDATE, DELETE, and MERGE statements that operate specifically on database tables. Occasionally, SELECT statements are included in the SQL DML category.

Note

It could be argued that SELECT statements do technically manipulate data when a query is performed, but in this book, we will differentiate between reading database tables and changing database tables. DBAs may configure and tune a mostly read-only database differently than they configure a frequent read-write database. An online transaction processing (OLTP) database would be considered a mostly read-write database. A decision support system (DSS) or data warehouse database would be considered a mostly read-only database.

The following sections provide an introduction to the DML statements UPDATE, INSERT, DELETE, and MERGE.

The UPDATE Statement

An UPDATE statement will change one or more rows in a database table. The basic form of an UPDATE statement must specify which table to update, which column(s) to change, and optionally, whether to change all the rows in the table or just a few. The syntax is as follows:

UPDATE  tablename SET column = value [ , column = value, ...]    [WHERE condition];

As with any SQL statements that access a table, the table to be updated must be owned by the user running the query or have the permissions granted to the user by the owner or a DBA. Chapter 11, “Users and Security,” will cover privileges and permissions in more detail.

Since a table may have a large number of columns, you don’t necessarily want to update every column. To follow up on an earlier example, let’s say that the boss has decided to give a 15% salary increase across the board. We can use an UPDATE statement that looks very similar to the SELECT statement we wrote earlier. Here are what the UPDATE statement and the result of executing that statement in iSQL*Plus look like:

But wait, you ask, did something actually happen here? The only clue is at the bottom of the screen, where it indicates that 14 rows were updated. DML statements such as UPDATE will perform the action requested (or produce an error message on occasion), but only SELECT statements will return rows to the user. To see if the rows were updated correctly, the user SCOTT will need to rerun the SELECT query on the EMP table.

Now that all the employees have been granted their raise, the boss decides that there are still some employees who need an even bigger raise. For example, employee FORD had a lot more bright ideas last year than the average employee, so he deserves another 10% raise above and beyond the 15% raise that he already received. Also, the boss notices that the employee file has not yet been updated with her employee information after the previous boss left late last month. Both of these changes require UPDATE statements that contain a WHERE clause to narrow down the number of changed records based on the employee name. Using iSQL*Plus, we can perform these two updates at once. Here are the results of the two UPDATE operations.

Notice that the results of both UPDATE statements appear at the bottom of the iSQL*Plus browser window.

The INSERT Statement

Whenever new employees are hired in Scott’s widget company, new rows must be added to the EMP table. The INSERT statement does just that. Here’s the basic INSERT syntax:

INSERT INTO tablename [(column1 [, column2 ...])]    VALUES (value1 [, value2 ... ]);

This format of the INSERT statement inserts only one row at a time. In Scott’s company, the boss realizes that she should probably leave the old boss’s employee information intact, and just add herself as a new row in the table. To handle this for her, we need to perform both an UPDATE and an INSERT on the EMP table. The two statements and their results are as follows:

update EMP set ENAME = ‘KING’ where ENAME = ‘QUEEN’; insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,     SAL, COMM, DEPTNO) values (7878, ‘QUEEN’, ‘PRESIDENT’, NULL, ‘15-AUG-2002’,     7500, NULL, 10);    1 row updated.  1 row created.

Notice that while the case of the keywords and column names is important only for readability, the text within the single quotation marks is case sensitive and must represent the exact text to be searched or the exact text to be inserted into the table’s column.

Warning

It is technically possible to create a column name with mixed case, but this technique is not recommended. This is because the column name must be specified with the same exact case in double quotation marks whenever it is referenced in any SQL command.

What does the NULL value mean? NULL is a special keyword that means literally nothing. It is not the same as a blank or an empty string. It means that the value inserted for this column in this row is unknown or not applicable. When this value is displayed as the result of a SELECT statement, it displays with blanks. In the case of the MGR column, the PRESIDENT employee has no boss, so this column is NULL for the former employee KING and the current employee QUEEN. The format for date columns—in this case, for the column HIREDATE—will be explained in Chapter 3, "Oracle Database Functions."

The DELETE Statement

As the name implies, the DELETE statement will remove rows from a database table. You can delete all rows or use a WHERE clause to specify rows, similar to the UPDATE statement. Here’s the syntax:

DELETE [FROM] tablename    [WHERE condition];

The FROM keyword is optional, but it makes the DELETE statement more readable (otherwise, it looks like you’re deleting the table itself!). In the case of Scott’s company, all of the employees hired in the last recruitment drive on August 18, 2002 and added to the EMP table will be working for the company’s subsidiary instead, so they must be deleted from the EMP table. Here’s the DELETE statement to accomplish this:

The MERGE Statement

The MERGE statement is new for Oracle9i, and it performs an operation that could be called an "upsert." It combines two operations that would normally need to be performed separately—an INSERT or an UPDATE—depending on whether the row already exists in the table.

Combining these two operations not only makes the application developer’s coding more straightforward (by not needing to perform an explicit compare operation with multiple UPDATE and INSERT statements), but it also reduces the number of operations performed on the table. These operations are also performed internally to the database, which makes the operation even more efficient because the additional statement parsing does not need to occur. The syntax is as follows:

MERGE INTO tablename alias    USING (tablename2 | view | subquery) alias2    ON (join_condition)    WHEN MATCHED THEN       UPDATE SET  col1 = col1_value [, col2 = col2_value ... ]    WHEN NOT MATCHED THEN       INSERT (column_list) VALUES (column_values); 

The basic syntax is fairly straightforward and easy to use. When the source table and the target table match on one or more columns (in the join_condition), the row is updated with an UPDATE statement; otherwise, the row is inserted with an INSERT statement. Many of the components of the MERGE statement, such as view and subquery, will be covered in later chapters.