12.13. SQL: Updating Table Populations

Generically, an “update” of the population of the database tables may involve any of three relational operations: insert a row into a table, delete a row from a table, or modify one or more values on a row. In SQL, the modify operation is called “update” so the term is sometime used in this restricted sense.

Let’s first consider how to insert rows into a table. For this, SQL provides an insert statement in two basic forms. The first adds a single row of data to a table. Its SQL-89 syntax is:

insert into tablename [ (col-list) ]
   values[constant-list)

If no columns are listed, values for all columns must be given. Character strings must be delimited by single quotes. A null value may be entered as null. SQL-92 introduced the following form of the insert statement: insert into tablename default values. This adds a single row where each column is given its default value (possibly null). An error results if any column has no default. SQL: 1999 added options for using constructors and overrides.

As a simple example, consider the table scheme Employee (emoNr. surname, firstname, gender, [phone], [email]). Suppose a new female employee named “Eve Jones” is assigned the employee number 715. To add these details of the new employee to the table, we could use either

insert into Employee
  values (715, ‘Jones’, ‘Eve’, ‘F, null, null)

or

insert into Employee (empNr, surname, firstname, gender)
  values (715, ‘Jones’, ‘Eve’, ‘F’)

Commercial systems also provide commands for fast bulk load of data from files (e.g., in delimited ASCII format). Data entry from users is typically via screen forms rather than directly to tables. Because of constraints on forms or between tables, a single row insert might require other data to be inserted in the same transaction. A transaction is a series of SQL statements, terminated by a commit statement:

commit [work]

If not committed, a transaction may be cancelled, thus:

rollback[work]

SQL: 1999 allows inclusion of one or more savepoints within a transaction. Work can then be rolled back to a specific savepoint rather than rolling back the whole transaction. Releasing a savepoint acts like a tentative commit of the part of the transaction preceding the savepoint. Many SQL vendors do not yet support savepoints.

The most powerful form of the insert statement allows us to insert multiple rows into a table in one go. Its basic syntax is:

insert into tablename [ (col-list) ]
  select-query

This inserts the set of rows returned from the subquery. This has many uses, such as making copies of tables, or temporarily storing join results to speed up later queries. For example, consider the software retailer database schematized earlier in Figure 12.63. Now suppose we want to run many queries about sales of the database products. To facilitate this, we could create an appropriate table called “DBsales” and then populate it, thus:

insert into DBsales
  select *
  from Invoice natural join Lineltem natural join Item
  where category = ‘DB’

For analysis purposes for a given period (e.g., 1 week), it may be acceptable to ignore updates to the underlying base tables. Queries can now be formulated on DBsales without the performance overhead of the original joins. This approach is used in data warehousing.

Now let’s consider how to delete rows. In SQL, the delete statement is used to delete some or all rows from a table. After deletion, the table structure still exists (until the relevant drop table command). The basic syntax of the delete statement is:

delete from tablename
[where condition]

Here are two examples, based on our retailer database:

delete from Item                  --deletes all rows from the Item table

delete from Item

where category = ‘DB’  --deletes all the database item rows from Item

Next, let’s consider how to update rows (i.e., change one or more values in existing rows). In SQL, the update statement is used to modify rows. Its basic syntax is shown. In SQL-89, expression must be a scalar expression, or null or default. A scalar expression may include column names, constants, and scalar operators.

update tablename
   set colname = expression[,...]
   [where condition]

As an example, consider the table scheme Employee (emoNr...., job, salary). The following statement gives all modelers earning less than $50 000 a 5% increase in salary:

update Employee
   set salary = salary * 1.05
   where job = ‘Modeler’
      and salary < 50000

Because of the bulk updating power of insert, delete, and update commands, it is critical to have facilities for undo (rollback) and security (e.g., granting relevant access rights).