3.4.1. Inserting Data

3.4.2. Deleting Data

For example, a cascading delete applied to rows in the megaliths table would also need to remove the appropriate rows in the media table where the following join condition is true:

megaliths.id = media.megaliths_id

However, DELETE statements do not have the same "single row at a time" restriction that INSERT statements suffer from. DELETE can purge a table entirely in one statement. For example, to remove all the rows within the megaliths table, we could simply write:

DELETE FROM megaliths

Of course, we may not wish to remove all the rows from a table, but only certain rows. This can be done in a familiar manner by specifying a list of conditions that the data within a row must meet for it to be removed. Therefore, if we wanted to delete all the rows of data within the megaliths table that contain sites located in ``Wiltshire,'' then the following statement would work nicely:

DELETE FROM megaliths
WHERE location LIKE '%Wiltshire%'

To remove all the rows relating to stone circles, we could narrow the criterion that a row must meet by saying that the type of the site must be equal to ``Stone Circle.'' The tighter query would read:

DELETE FROM megaliths
WHERE location LIKE '%Wiltshire%'
AND site_type_id IN (SELECT id FROM site_types
                     WHERE site_type = 'Stone Circle')

A more powerful way to determine which rows of data to delete can be expressed by using a subquery to return the target rows. A good example of this sort of behavior is deleting the foreign keys from a table when the primary keys are being deleted. This can be broken up into two separate DELETE statements, the first removing the foreign key rows, the second removing the primary key rows. The following examples remove the rows relating to megalithic sites in ``Wiltshire'' from both the media and megaliths tables:

DELETE FROM media
WHERE megaliths_id IN (
    SELECT id
    FROM megaliths
    WHERE location LIKE '%Wiltshire%'
  )

DELETE FROM megaliths
WHERE location LIKE '%Wiltshire%'

3.4.3. Updating Data

UPDATE statements are extremely powerful, in that it is possible to update multiple rows of data in one statement. If desired, the new values may be supplied by the returned values from a SELECT statement following the syntax of the INSERT command.

The most simple and useful UPDATE is to update a column of a single row within a table to a new value. For example, if you wanted to update the location of the "Avebury" row within the megaliths table, the following SQL statement would work:

UPDATE megaliths
SET location = 'Near Devizes, Wiltshire'
WHERE name = 'Avebury'

You should notice the condition clause specified in this statement. If the statement did not check for the exact name of the site, every row within the table would have had the UPDATE statement performed against it, causing a potentially disastrous data corruption. Condition clauses may be specified in exactly the same way as used in other SQL commands such as DELETE and SELECT.

UPDATE statements may also update more than one column in a single statement, by simply listing the columns we wish to update in a comma-separated list. For example, to update both the name and description fields within the megaliths table, you can write the following SQL statement:

UPDATE megaliths
SET location = 'Callanish, Isle of Lewis',
    description = 'Complex site shaped as a buckled Celtic cross'
WHERE name = 'Callanish I'

In some database systems, it is also possible to update multiple columns simultaneously by using a subquery to return a list of values from another table. Those values are then used as the new values for the specified columns. For example, if we wanted to synchronize our megalithic database with the Wiltshire Tourist Board Database to use the same names and locations, we could use the following SQL statement:

UPDATE megaliths
SET ( name, location ) =
    ( SELECT tourist_site_name, tourist_site_location
      FROM tourist_sites
      WHERE tourist_site_name LIKE '%Avebury%'
      AND tourist_site_type = 'Stone Circle' )
WHERE name = 'Avebury'

This statement would update the name and location fields within the megaliths table with values returned by a query running against another table. An important note about this technique is that the subquery must return only a single row of data, otherwise the UPDATE will fail with an error.

3.4.4. Committing and Rolling Back Modifications

The principle of rollback is quite a simple one. For each modification to rows of data within the database, a copy of the row prior to modification is written into a log that records all the modifications made. Once you have decided that these changes are indeed correct, you can opt to commit the changes to the database. If the committed changes are actually wrong, then you're in trouble: you can clear out your desk and dust off your resume.

However, if by some sheer luck you check the modified rows and see that they are wrong before you commit the changes, you can rollback the modifications you have made, returning the rows to the values they held before you started modifying them. Your job is safe.

Even better, the changes you made within the transaction were not visible to anyone else looking at the database at that time. So no one need know of your mistake, and your reputation is safe.