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) ;
| rule_id | rule_name | priority |
|---|---|---|
| 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.
| rule_id | rule_name | priority | rule_id | rule_name | priority |
|---|---|---|---|---|---|
| 1 | Take one bread | 10 | 4 | Wash hand | 40 |
| 4 | Wash hand | 40 | 1 | Take one bread | 10 |
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.
For PostgreSQL you should use DISTINCT FROM I believe… in the last statement of course.
The Cartesian product will result a combination of (1, 1), (1,4), (4,1), (4,4). The WHERE condition “rules.rule_id rule1.rule_id” will get you (1, 4) and (4, 1). So the WHERE condition guarantees distinct, unless I’m missing something.
It can be done with a few where-conditions less:
update rules
set priority = rule1.priority
from rules rule1
where rules.rule_id in (1, 4)
and rule1.rule_id + rules.rule_id = 1 + 4;
Small little comment. I think the PostgreSQL create table statement to more mirror the MySQL is
CREATE TABLE rules (
rule_id serial PRIMARY KEY,
rule_name varchar(255) NOT NULL,
priority integer
) ;
the without oids can be left out since its the default behavior for newer versions.
This method does not work when we have unique constraint/index on the given column
Yes … I realize that th method doesn’t work when there id unique constraint. I’ve tried many ways and I ended up having to do it in multiple statements.
why not to simplify final query with:
01.UPDATE
02. rules
03.SET
04. priority = rule1.priority
05.FROM
06. rules AS rule1
07.WHERE
08. rules.rule_id IN (1,4)
09. AND rule1.rule_id IN (1,4)
10. AND rules.rule_id <> rule1.rule_id
11.;
Hi Guys, I m beginner in SQL.
Can any tell me how can i swap “Take one bread to Wash hand” and “Wash hand to Take one bread” in Above table.
Thanks for this!
Just in case you\’re curious:
For a categorized faq system I created the following table along with a couple of other columns:
id | parent | sortorder | content
—-+——–+———–+————————
1 | 0 | 1 | Category 1
—-+——–+———–+————————
2 | 1 | 1 | Category 1, subitem 1
—-+——–+———–+————————
3 | 1 | 2 | Category 1, subitem 2
—-+——–+———–+————————
4 | 1 | 3 | Category 1, subitem 3
—-+——–+———–+————————
5 | 0 | 2 | Category 2
—-+——–+———–+————————
6 | 5 | 1 | Category 2, subitem 1
—-+——–+———–+————————
Guess, you get the idea.
Now, in the backend, I wanted to implement an option to move items up and down. Modifying the query from here, I came up with this:
UPDATE $table AS hc1 JOIN $table AS hc2 ON ( hc1.id = $id AND hc2.sortorder = hc1.sortorder+1 AND hc2.parent = hc1.parent ) SET hc1.sortorder = hc2.sortorder, hc2.sortorder = hc1.sortorder;
$table is (obviously) the table name, while $id is the ID of the item you want to move. The above query moves the item with $id down. For the other way around, change the + to a -.
I just wanted to share that.
Problem with this code (while being very good) is that it relies on you neighbouring row having a id number -1 or + 1 OR knowing the id number of the row you’re swapping with.
What happens when that neighbouring row has been deleted? – What do you do when there is several numbers between?
Is there no order by asc / desc trick that can be used here?
@Jimbo: I don’t really get what you mean. You just need some kind of ID of the 2 rows to be swaped and swap the priority. If ID 1 with priority 19938 to be swapped with ID 48 with priority 38984, it really doesn’t matter if there’s priority 19937 or 19939 or 38983 or 38985.
The end result is ID 1 previously has priority 19938 should now have priority 38984. And ID 48 previously has priority 38984 should now have priority 19938.
He\’s speaking about moving a row in the \"list\" not just up/down. For example if you have:
1 a
2 b
3 c
4 d
5 e
and moves d to 2, all the rows between d old_position and d new_position will have there position +=1 (including the one where d has moved to). So:
1 a
2 d
3 b (+1)
4 c (+1)
5 e
You have to care about all cases (delete, insert, moving to higher position, or to lower) but actually this does not make sense here since as you say we only do a swap between 2 rows.
But I had to do it to implement a task manager and found this article while looking for swapping two rows.
I also need to swap two entries with a unique constraint.
I am a big fan of mental challenges, moreover – in my case I need to perform it in MySql level due to some php limitations.
But . . as IVO GELOV and Maresa said before, I don’t think that this is possible.
If somebody has an idea, I will be glad to hear!! (although probably till then I will find another solution…)
Easily done
UPDATE
rules AS rule1
JOIN rules AS rule2 ON
( rule1.rule_id = 1 AND rule2.rule_id = 4 )
JOIN rules AS rule3 ON
( rule2.rule_id = 4 AND rule3.rule_id = 1 )
SET
rule1.priority = rule2.priority,
rule2.priority = rule3.priority
very cool! easy to understand