I encountered a situation at work that I feel would be a good brain teaser. I was working on a project and needed a way to swap values from 2 different rows in a SQL table. Obviously I can do it on client side (like PHP) to do it; but that wouldn’t be a good brain teaser.

The problem

So here is the description of the problem. Suppose you have a table with the following data:

-- For MySQL
CREATE TABLE `rules` (
	`rule_id` int(10) unsigned NOT NULL auto_increment,
	`rule_name` varchar(255) NOT NULL,
	`priority` int(11) default NULL,
	PRIMARY KEY  (`rule_id`)
);

-- For PostgreSQL
CREATE TABLE rules (
	rule_id integer NOT NULL,
	rule_name character varying NOT NULL,
	priority integer,
	CONSTRAINT rules_pkey PRIMARY KEY (rule_id)
) WITHOUT OIDS;

INSERT INTO rules
	(rule_id, rule_name, priority)
VALUES
	(1, 'Take one bread', 10),
	(2, 'Drink water',    20),
	(3, 'Eat candy',      30),
	(4, 'Wash hand',      40),
	(5, 'Give charity',   50)
;

Let’s say you need to swap priority between rule 1 (Take one bread) and rule 4 (Wash hand). After all, you should wash your hands first before doing anything rite?

The solution

Quick and dirty way

What I mean is that this solution is not challenging at all. If you’re working on PHP, one way to do this is someting like below:

$result = mysql_query("SELECT priority FROM rules WHERE rule_id = 1");
$row1 = mysql_fetch_row($result);

$result = mysql_query("SELECT priority FROM rules WHERE rule_id = 4");
$row2 = mysql_fetch_row($result);

mysql_query("UPDATE rules SET priority = {$row2[0]} WHERE rule_id = 1");
mysql_query("UPDATE rules SET priority = {$row1[0]} WHERE rule_id = 4");

Well, the solution above is totally not exciting. Moreover, it uses 4 SQL statements. In a heavily loaded system where updates are happening a lot, there may be an issue with such approach. For example, there may be an update to rule_id 1 priority after we select. Hence, the result that we got for rule_id 1 is already out dated.

There are ways to the shortcomings of the method above. For example, you can use transactions and add “FOR UPDATE” keyword on the select. If you’re on MySQL, your table must have InnoDB as the storage engine.

The elegant way

Well, it is elegant according to me at least. I do think so because this solution doesn’t use any sub queries and it follows the standard multi-table form of UPDATE statement.

Often times when I’m doing update for multiple tables, I first try to come up with the SELECT statement first. Once I’m happy with the SELECT statement, it’s easy to convert it to an UPDATE. So let’s do just that.

I figure that I somehow need to get the data from the 2 rows to be swapped in 1 row. That way I can simply do an update. So my goal is to try to come up with a SELECT statement that gives the following table as the result.

To achieve the result above, I need to do a Cartesian self join on rules table. Here’s the query that I came up with:

SELECT
	*
FROM
	rules AS rule1
	JOIN rules AS rule2 ON
		   ( rule1.rule_id = 1 AND rule2.rule_id = 4 )
		OR ( rule1.rule_id = 4 AND rule2.rule_id = 1 )
;

Once I got the SELECT query, I can simply turn it into an update statement like below:

UPDATE
	rules AS rule1
	JOIN rules AS rule2 ON
		   ( rule1.rule_id = 1 AND rule2.rule_id = 4 )
		OR ( rule1.rule_id = 4 AND rule2.rule_id = 1 )
SET
	rule1.priority = rule2.priority,
	rule2.priority = rule1.priority
;

Update: A friend of mine brought up a nice optimization on the query above. He said that the OR condition on line 5 can be eliminated.

For PostgreSQL, the query is a little bit different. The basic idea is the same. We’re still using Cartesian self join. The difference is the syntax. Here’s PostgreSQL version.

UPDATE
	rules
SET
	priority = rule1.priority
FROM
	rules AS rule1
WHERE
	    ( rules.rule_id = 1 OR rules.rule_id = 4 )
	AND ( rule1.rule_id = 1 OR rule1.rule_id = 4 )
	AND rules.rule_id <> rule1.rule_id
;

Obviously there are other ways to do it, like using sub queries, etc. But I think, the most elegant way is if we can get as close to the UPDATE statement form as possible. I hope you enjoyed this article. Please leave comments / suggestions / questions. I’m looking forward to improving my solution with your comments / suggestions / questions.