Swap Two Column Values in SQL: Part 1

I recently needed to write a PostgreSQL snippet that would massage some
data. More specifically, I needed to swap the ordering of two different
records in the pages table. These records are made up of an id, a
name, and an ordering.

Here is a snapshot of the data in pages.

select * from pages order by ordering;
 id |  name  | ordering
----+--------+----------
  3 | Green  |        1
  1 | Red    |        2
  4 | Yellow |        3
  2 | Blue   |        4

We've been informed that we need to swap the ordering of Red and Blue.
We can see here in this snapshot of the data that Red has an ordering of
2 and Blue has an ordering of 4.

Doing a swap based on the data that we have locally would be most easily
accomplished with a couple update statements.

update pages set ordering 
4 where name = 'Red'; update pages set ordering = 2 where name = 'Blue';

Unfortunately, we've been informed that these exact ordering values cannot
be guaranteed in the primary data set. All we know is that Red and Blue
need to be swapped.

So, how do we write a general purpose swapping statement that works both
with our local dataset and with the primary data set?

We can combine the updates into a single statement doing the value swapping
based on the evaluation of a case statement that utilize subqueries.

update pages
set ordering = case name
                 when 'Red' then (select ordering from pages where name = 'Blue')
                 when 'Blue' then (select ordering from pages where name = 'Red')
               end
where name = 'Red' or name = 'Blue';

We can run it and then check that it works:

select * from pages order by ordering;
 id |  name  | ordering
----+--------+----------
  3 | Green  |        1
  2 | Blue   |        2
  4 | Yellow |        3
  1 | Red    |        4

But how does it work?

At first glance, it seems like updating one record's ordering could mean
that we clobber the ordering value that we need in order to update the
other. Shouldn't we need some sort of temporary variable? We've probably
seen imperative code statements that look something like the following:

let temp = a
a = b
b = temp

Why don't we need a temporary value with our PostgreSQL statement?

The answer has to do with the consistency guarantees that PostgreSQL
provides us. Throughout the entire course of a single update statement,
PostgreSQL is going to provide a single, consistent snapshot of our data. If
this wasn't the case, then the subqueries inside of the case statement
would have data changing out from underneath them.

This technique not only massages our data as requested, it shows us the
dynamism of update statements in PostgreSQL. We are able to utilize case
statements and subqueries to get exactly what we need out of the statement.
This statement isn't flawless though. In part 2 of this post, we'll explore
another use case and see how we can build on this technique.


Supporting SQL

Create the pages table.

create table pages (
  id serial primary key,
  name varchar not null,
  ordering integer not null
);

Insert some records into the pages table.

insert into pages (name, ordering)
values
  ('Red', 2),
  ('Blue', 4),
  ('Green', 1),
  ('Yellow', 3);

Cover image by Dmitri Popov on Unsplash.com

Shout out to Jake Worth with whom I worked on this particular solution.

Leave a Reply

Your email address will not be published. Required fields are marked *