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,
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
Let's add that to ensure that no two pages can end up with the same
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
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
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
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
swap_table, the values and their keys (the
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
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.
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
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
swap_table based on corresponding
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
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.
create table pages ( id serial primary key, name varchar not null, ordering integer not null );
Insert some records into the
insert into pages (name, ordering) values ('Red', 2), ('Blue', 4), ('Green', 1), ('Yellow', 3);
Cover image by Dmitri Popov on Unsplash.com