Swap Two Column Values in SQL: Part 2

In the first part of this post we looked at a technique for swapping two
column values for existing records in a single statement. It is a relatively
sound solution that takes advantage of subqueries and a case statement.
Unfortunately, it has a couple drawbacks which we will address in this post.

This post builds off of Part 1. If you haven't already, give it a
read.

The first drawback can be demonstrated by building on our existing example,
the pages table which has the following data.

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

In the interest of maintaining data integrity, it is reasonable to expect
that a table like this would have a uniqueness constraint on ordering.
Let's add that to ensure

no two pages can end up with the same
ordering value.

alter table pages add constraint orderings_are_unique unique (ordering);
ALTER TABLE

With that in place, let's see how our update statement from the previous
post holds up.

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';
ERROR:  duplicate key value violates unique constraint "orderings_are_unique"
DETAIL:  Key (ordering)=(4) already exists.

Though this single query may have been able to rotate those values in place,
it runs into trouble when confronted with a uniqueness constraint. What to
do?

One possible solution to this I picked up from my colleague, Jack
Christensen. It will allow us to swap the values despite the uniqueness
constraint and still maintains that we don't need to know the exact
ordering values at the time we write the query.

update pages set ordering = -ordering where name = 'Red' or name = 'Blue';
UPDATE 2
select * from pages order by ordering;
 id |  name  | ordering
----+--------+----------
  2 | Blue   |       -4
  1 | Red    |       -2
  3 | Green  |        1
  4 | Yellow |        3

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';
UPDATE 2
select * from pages order by ordering;
 id |  name  | ordering
----+--------+----------
  3 | Green  |        1
  2 | Blue   |        2
  4 | Yellow |        3
  1 | Red    |        4

This solution uses two statements. The first negates the values that will be
swapped. The second makes them positive again as it swaps them using the
same technique from the previous approach. The negative values act as
lossless placeholders to prevent violation of the uniqueness constraint.

This is a bit of a trick that takes advantage of two key facts. First,
ordering is an integer column, so it can be negated. Second, we are
assuming that only the range of positive numbers represented by this integer
column are being utilized. If this is the case, then at any point in time we
can use the range of negative numbers as a temporary scratch pad for
swapping these values.

This frames the second drawback. What do we do if the column we are swapping
has a unique constraint but is not an integer column?

To illustrate this, let's also put a uniqueness constraint on the name
column which we will now treat as the target of the swap.

alter table pages add constraint names_are_unique unique (name);
ALTER TABLE

In this scenario, we want to swap the name values of the pages that have
ordering values of 2 and 4. This is going to take a number of steps,
so let's walk through them one by one.

First, we create a temporary table that we call the swap_table. The
intention of this table is to serve as the temporary scratch pad that holds
on to the values that need swapping. In fact, in the subsequent insert
statement into swap_table, the values and their keys (the ordering
value) are inserted in the swapped order.

create temporary table swap_table (swap_key integer, swap_value varchar);
CREATE TABLE
insert into swap_table (swap_key, swap_value)
select p1.ordering, p2.name
from pages p1
join pages p2 on p1.name != p2.name
where p1.ordering in (2,4)
  and p2.ordering in (2,4);
INSERT 0 2
table swap_table;
 swap_key | swap_value
----------+------------
        2 | Blue
        4 | Red

To see really clearly how this swapped order is achieved for the
swap_table, let's isolate just the select statement. Because we are
only dealing with two records and we know the values are unique, we can join
the table against itself based on the name values not being equal.

select
  p1.ordering,
  p1.name as original,
  p2.name as swapped
from pages p1
join pages p2 on p1.name != p2.name
where p1.ordering in (2,4) andp2.ordering in (2,4);

 ordering | original | swapped
----------+----------+---------
        2 | Red      | Blue
        4 | Blue     | Red

This swap_table is necessary in order to deal with the uniqueness of the
name column as well as the possibility that it is a not null column.

Because we cannot simply negate the original name values as we did in the
integer example, we need to find another way to uniquely obfuscate them.
Postgres' built-in
md5()
function is a great choice here since we are dealing with string values. We
update the target records in the pages table accordingly. Again, this is
so that we avoid violating the uniqueness constraint on name in the
subsequent update statement.

update pages set name = md5(name) where ordering in (2,4);
UPDATE 2
select * from pages order by ordering;
 id |               name               | ordering
----+----------------------------------+----------
  3 | Green                            |        1
  1 | ee38e4d5dd68c4e440825018d549cb47 |        2
  4 | Yellow                           |        3
  2 | 9594eec95be70e7b1710f730fdda33d9 |        4

Then we update the target records in pages with the swapped values from
the swap_table based on corresponding ordering values.

update pages
set name = swap_table.swap_value
from swap_table
where swap_table.swap_key = ordering;
UPDATE 2

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

Lastly, we can drop our temporary table in an effort to be tidy. Because it
is a temporary table, we can also count on it being cleaned up when the
current connection is terminated.

drop table swap_table;
DROP TABLE

The queries needed for this scenario may feel overwhelming, but they
demonstrate the power and flexibility of SQL and, in particular, PostgreSQL.

In this post we saw a couple tricky ways of swapping column values when
constrained by uniqueness constraints in the case of both integers and
strings. We took advantage of the range of negative numbers that is often
ignored for an integer column, we created a temporary table, and even used
the md5() string function provided by Postgres. Surely none of these
solutions are a perfect fit for every situation, but what they demonstrate
is the capacity that SQL has for solving all kinds of problems that arise.


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

Leave a Reply

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