Hack 4. UPDATE the Database

The behavior of UPDATE can seem confusing to people accustomed to procedural programming languages such as Perl and Java. Learn how UPDATE works, and why.

In most programming languages, you need a temporary variable if you want to swap the values of two variables. Suppose you want to move the players around in your netball team. Let the wing attack have a go as goal shooter and put the goal shooter on wing attack:

In an SQL UPDATE statement, you don't need the temporary variable. The values on the right of the = are consistent throughout the whole UPDATE statement; it is as though all of the updates happened simultaneously rather than one after another. Here is the result of swapping the two positions in Oracle; you will get the same result if you try it on SQL Server or on PostgreSQL (read on for MySQL):

This is rather like the Perl construct that allows you to assign a list of variables in a single statement:

($goalShooter,$wingAttack) = ($wingAttack,$goalShooter);

When a relational database performs an update it has to maintain a copy of all of the original values in some place to ensure isolated transactions. A single UPDATE statement might involve thousands of rows and might take several minutes to complete. If there were a failure during the update (if someone switched off the computer, for example), the system is guaranteed to roll back and none of the changes will be committed.

The system has access to all of the values prior to the first change happening. Also, you cannot normally predict the order in which the updates take place, so the sensible behavior is to apply changes relative to the original values and not take account of changes that take place during execution of the command.

1.4.1. MySQL Differences

MySQL is the exception to the rule. In MySQL, the updates are done in sequence from left to right, so the preceding SQL query produces a different result in MySQL:

This causes a problem. In a procedural programming language, you would simply use a temporary variable, but you do not have a temporary variable in an UPDATE statement in SQL. Fortunately, there is an algorithm that will swap two numeric fields without a temporary variable. To swap x and y, for instance, you can use SETx=x+y,y=x-y,x=x-y. It's easier to see what is going on by looking at an example (see Table 1-1). Suppose x is 100 and y is 1.

Table 1-1. Swap x and y without a spare, step by step

Statement

X value

Y value

(Initial state)

100

1

x=x+y

101

1

y=x-y

101

100

x=x-y

1

100

Let's change from using named players to using numbers (for instance, Camelia becomes 101):