SQL and Relational Theory, 2nd Edition

UPDATING IS SET LEVEL

The first point I want to stress is that, regardless of what syntax we use to express it, relational assignment is a set level operation. (In fact, all operations in the relational model are set level, meaning they take entire relations or relvars as operands, not just individual tuples.) Thus, INSERT inserts a set of tuples into the target relvar; DELETE deletes a set of tuples from the target relvar; and UPDATE updates a set of tuples in the target relvar. Now, it’s true that we often talk in terms of (for example) updating some individual tuple as such, but you need to understand that:

1. Such talk really means the set of tuples we’re updating just happens to have cardinality one.

2. What’s more, updating a set of tuples of cardinality one sometimes isn’t possible anyway.

For example, suppose relvar S is subject to the integrity constraint (see Chapter 8) that suppliers S1 and S4 are always in the same city. Then any “single tuple UPDATE” that tries to change the city for just one of those two suppliers will necessarily fail. Instead, we must change them both at the same time, perhaps like this:

What’s being updated in this example is a set of two tuples.

One consequence of the foregoing is that there’s nothing in the relational model corresponding to SQL’s “positioned updates” (i.e., UPDATE or DELETE “WHERE CURRENT OF cursor”), because those operations are tuple level ...