Hack 12. Simplify Complicated Updates

You can perform complex calculations with an UPDATE, which can save you from having to use a cursor or from doing the calculations outside the database.

The UPDATE example shown in many introductory textbooks is a simple operation whereby you raise everyone's salary by $100:

UPDATE employee
 SET salary = salary + 100

That's certainly a simple statement, but it is likely to be too simple to be of any use. Let's suppose that annual wage negotiations resulted in a more complex deal that requires you to access other tables in the database.

Employees who have a clean disciplinary record will get the $100 raise; those with a single offense will keep the same salary; those with two or more recorded transgressions will get a $100 cut in salary. The employee details and the disciplinary records are held in the employee and disciplinary tables:

mysql> SELECT * FROM employee;
+----+----------+---------+
| id | name | salary |
+----+----------+---------+
| 1 | Reginald | 5000.00 |
| 2 | C J | 5000.00 |
| 3 | Joan | 5000.00 |
+----+----------+---------+

mysql> SELECT * FROM disciplinary;
+------------+-----+
| whn | emp |
+------------+-----+
| 2006-05-20 | 1 |
| 2006-05-21 | 1 |
| 2006-05-22 | 3 |
+------------+-----+

You could write a complex UPDATE statement that updates the employee table while referencing the disciplinary table, but it is easier to do this in two stages. First, prepare a view that calculates the new values and then apply those changes with an UPDATE.

The newSalary view includes two columns: the primary key of the table to be updated (employee) and the new value of the salary. You can preview the result of this view before executing a simple UPDATE to transfer the new values into place.

You can define the view that contains the new salaries for every employee as follows:

mysql> CREATE VIEW newSalary AS
 -> SELECT id, CASE WHEN COUNT(emp) = 0 THEN salary+100
 -> WHEN COUNT(emp) > 1 THEN salary-100
 -> ELSE salary
 -> END AS v
 -> FROM employee LEFT JOIN disciplinary ON (id=emp)
 -> GROUP BY id,salary;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM newSalary;
+----+---------+
| id | v |
+----+---------+
| 1 | 4900.00 |
| 2 | 5100.00 |
| 3 | 5000.00 |
+----+---------+

You can run this view and see the result, but the actual salary table will not have been updated yet. It might be a good idea to let someone double-check these values before the change is actually applied.

Applying the new salary requires a simple UPDATE statement. This is important because you need to be absolutely certain that the values in the view that have been checked are the values that will be applied:

mysql> UPDATE employee
 -> SET salary = (SELECT v FROM newSalary
 -> WHERE newSalary.id=employee.id)
 -> WHERE id IN (SELECT id FROM newSalary);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 3 Changed: 2 Warnings: 0

mysql> SELECT * FROM employee;
+----+----------+---------+
| id | name | salary |
+----+----------+---------+
| 1 | Reginald | 4900.00 |
| 2 | C J | 5100.00 |
| 3 | Joan | 5000.00 |
+----+----------+---------+
3 rows in set (0.00 sec)

 

2.6.1. Using a Cursor

You might be tempted to use a cursor from a programming language's database API [Hack #2] to run through a number of updates like this. The advantage of the update is that it is shorter, faster, and atomic. Whether it is easier to understand depends on the style of programming that you are used to. SQL, when used without cursors, is well suited to the declarative style.

2.6.2. Using a VIEW

You could run the update without creating a named view, but that makes it harder to preview the results. Another problem with complicated UPDATE statements is that they are inconvenient to debug: every time you test the UPDATE your data changes, so you need to reset it before your next test run. If you put the complexity into a view, you can check the results of your calculations without changing any values.