Deletion / Updates

Deletion / Updates

Deletion

A deletion statement consists of:

1. The keywords DELETE FROM,

2. The name of a relation, say R,

3. The keyword WHERE, and

4. A condition.

That is, the form of a deletion is

  

The effect of executing this statement is that every tuple satisfying the condition (4) will be deleted from relation R.

Example 1 : We can delete from relation

    StarsIn(movieTitle, movieYear, starName)

the fact that Sydney Greenstreet was a star in The Maltese Falcon by the SQL statement:

DELETE FROM StarsIn
WHERE movieTitle = The Maltese Falcon AND
              movieYear = 1942 AND
              starName = Sydney Greenstreet;

Notice that unlike the insertion statement of Database Modifications Example 1, we cannot simply specify a tuple to be deleted. Rather, we must explain the tuple exactly by a WHERE clause.

Example 2 : Here is another example of a deletion. This time, we delete from relation

    MovieExec(name, address, cert#, netWorth)

several tuples at once by using a condition that can be satisfied by more than one tuple. The statement

DELETE FROM MovieExec
WHERE networth < 10000000;

deletes all movie executives whose net worth is low - less than ten million dollars.

Updates

While we might think of both insertions and deletions of tuples as "updates" to the database, an update in SQL is a very specific kind of change to the database: one or more tuples that already exist in the database have some of their components changed. The general form of an update statement is:

1. The keyword UPDATE,
2. A relation name, say R,
3. The keyword SET,
4. A list of formulas that each set an attribute of the relation R equal to the value of an expression or constant,
5. The keyword WHERE, and
6. A condition.

That is, the form of an update is

   

Each new-value assignment (item 4 above) is an attribute, an equal sign, and a formula. If there is more than one assignment, they are separated by commas.

The effect of this statement is to find all the tuples in R that satisfy the condition (6). Each of these tuples are then changed by having the formulas of (4) evaluated and assigned to the components of the tuple for the corresponding attributes of R.

Example 3 : Let us modify the relation

    MovieExec(name, address, cert#, netWorth)

by attaching the title Pres. in front of the name of every movie executive who is the president of a studio. The condition the desired tuples satisfy is that their certificate numbers appear in the presC# component of some tuple in the Studio relation. We express this update as:

    1)   UPDATE MovieExec
    2)   SET name = 'Pres. ' | | name
    3)   WHERE cert# IN (SELECT presC# FROM Studio) ;

Line (3) tests whether the certificate number from the MovieExec tuple is one of those that appear as a president's certificate number in Studio.

Line (2) performs the update on the selected tuples. Remember that the operator | | denotes concatenation of strings, so the expression following the = sign in line (2) places the characters Pres. and a blank in front of the old value of the name component of this tuple. The new string becomes the value of the name component of this tuple; the effect is that Pres. ' has been prepended to the old value of name.


Tags