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:

/* The original lineup */
goalShooter = 'Camelia';
wingAttack = 'Rosie';

/* Swap goalShooter with wingAttack */
tmp = goalShooter;
goalShooter = wingAttack;
wingAttack = tmp;

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):

SQL> SELECT goalShooter,goalAttack,wingAttack FROM offenceTeam;

GOALSHOOTER GOALATTACK WINGATTACK
--------------------- --------------------- ---------------------
Camelia Demi Rosie

SQL> UPDATE offenceTeam
 2 SET goalShooter = wingAttack,
 3 wingAttack = goalShooter;

1 row updated.

SQL> SELECT goalShooter,goalAttack,wingAttack FROM offenceTeam;

GOALSHOOTER GOALATTACK WINGATTACK
--------------------- --------------------- ---------------------
Rosie Demi Camelia

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:

mysql> SELECT goalShooter,goalAttack,wingAttack FROM offenceTeam;
+-------------+------------+------------+
| goalShooter | goalAttack | wingAttack |
+-------------+------------+------------+
| Camelia | Demi | Rosie |
+-------------+------------+------------+
1 row in set (0.00 sec)

mysql> UPDATE offenceTeam
 -> SET goalShooter = wingAttack,
 -> wingAttack = goalShooter;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT goalShooter,goalAttack,wingAttack FROM offenceTeam;
+-------------+------------+------------+
| goalShooter | goalAttack | wingAttack |
+-------------+------------+------------+
| Rosie | Demi | Rosie |
+-------------+------------+------------+
1 row in set (0.00 sec)

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 SET x=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):

mysql> SELECT * FROM offenceTeamN;
+----------+-------------+------------+------------+
| teamName | goalShooter | goalAttack | wingAttack |
+----------+-------------+------------+------------+
| A | 101 | 102 | 103 |
+----------+-------------+------------+------------+
1 row in set (0.00 sec)

mysql> UPDATE offenceTeamN
 -> SET goalShooter = goalShooter+wingAttack
 -> , wingAttack = goalShooter-wingAttack
 -> , goalShooter = goalShooter-wingAttack;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
mysql> SELECT * FROM offenceTeamN;
+----------+-------------+------------+------------+
| teamName | goalShooter | goalAttack | wingAttack |
+----------+-------------+------------+------------+
| A | 103 | 102 | 101 |
+----------+-------------+------------+------------+
1 row in set (0.00 sec)

You can apply this idea to strings as well. However, instead of adding and subtracting, you need to use CONCAT and SUBSTRING_INDEX, making sure to use a separator that does not appear in the values:

UPDATE offenceTeam
 SET goalshooter = CONCAT(goalShooter,':',wingAttack)
 , wingAttack = SUBSTRING_INDEX(goalShooter,':',1)
 , goalShooter = SUBSTRING_INDEX(goalShooter,':',-1)