Bookmark and Share

Swap values in 2 rows SQL

Posted: Sunday, October 11th, 2009 at 11:31 amUpdated: Sunday, October 11th, 2009 at 9:11 pm

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.

16 Responses to “Swap values in 2 rows SQL”

  1. Pavel Golub Says:

    For PostgreSQL you should use DISTINCT FROM I believe… in the last statement of course.

  2. Maresa Says:

    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.

  3. Onno Molenkamp Says:

    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;

  4. Regina Says:

    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.

  5. IVO GELOV Says:

    This method does not work when we have unique constraint/index on the given column :(

  6. Maresa Says:

    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. :-(

  7. Vladimir Bilyov Says:

    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.;

  8. Manoj Says:

    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.

  9. wydi Says:

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

  10. Jimbo Says:

    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?

  11. Maresa Says:

    @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.

  12. Amida Says:

    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.

  13. Guy Says:

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

  14. Basil Says:

    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

  15. nick Says:

    very cool! easy to understand

  16. Abhishek Says:

    In Mysql:
    This query also work, I have tested it… Enjoy
    UPDATE
    rules AS rule1
    JOIN rules AS rule2 ON
    rule1.rule_id = 1 AND rule2.rule_id = 4
    SET
    rule1.priority = rule2.priority,
    rule2.priority = rule1.priority
    ;

Leave a Reply


nine − 5 =