Updating Is Set-at-a-Time

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, as we'll see in Chapter 5.) 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:

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

• 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 6) 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 update them both at the same time, perhaps like this (SQL):

     UPDATE S     SET    CITY = 'New York'     WHERE  S.SNO = SNO('S1') OR S.SNO = SNO('S4') ;

What's being updated here is, obviously enough, a set of two tuples.

NOTE

Here, for interest, is the same update expressed in Tutorial D (it looks very similar, as you can see):

     UPDATE S WHERE SNO = SNO('S1') OR SNO = SNO('S4')            ( CITY := 'New York' ) ;

One consequence of the foregoing is that there's nothing in the relational model that resembles SQL's "positioned updates" (that is, UPDATE or DELETE "WHERE CURRENT OF cursor"), because those operations are tuple-level, not set-level, by definition. They do happen to work in today's products, most of the time, but that's because those products aren't very good at supporting integrity constraints. If the products were to improve in that regard, those "positioned updates" might not work any more; that is, applications that succeed today might fail tomorrow not a very desirable state of affairs, it seems to me.

Now I need to 'fess up to something. The fact is, to talk as I've been doing of "updating a tuple" or set of tuples, rather is very imprecise (not to say sloppy) anyway. If V is subject to update, then V must be a variable by definition, not a value, and tuples, like relations, are values and can't be updated, again by definition. What we really mean when we talk of (for example) updating tuple t1 to t2, within some relvar R, is that we're replacing tuple t1 in R by another tuple t2. And that kind of talk is still sloppy! What we really mean is that we're replacing the relation r1 that's the original value of R by another relation r2. And what exactly is relation r2 here? Well, let s1 and s2 be relations containing just tuple t1 and tuple t2, respectively; then r2 is (r1 MINUS s1) UNION s2. In other words, "updating tuple t1 to t2 in relvar R" can be thought of as first deleting t1 and then inserting t2 if (despite everything I've been saying) I might be permitted to talk in terms of deleting and inserting individual tuples in this loose fashion.

In the same kind of way, it doesn't really make sense to talk in terms of "updating attribute A within tuple t" or within relation r, or even within relvar R. Of course, we do it anyway, because it's convenient (it saves a lot of circumlocution); but it's like that business of user-friendly terminology I discussed in Chapter 1 it's OK to do it only if we all understand that such talk is only an approximation to the truth, and indeed it tends to obscure the essence of what's really going on.