If you have a db table with a unique column (like "name"), how would you go about switching the names of two rows? (Using Hibernate, but it sounds like a problem across the board)

Question

Let's say you had a table like this

/------------
| id | name |
|------------|
| 1 | foo    |
| 2 | bar    |
-----------

There is a uniqueness constraint on the 'name' column.

How would you set #2's name to foo, and #1's name to bar?

I know you could probably just assign temporary names to both then stick in the desired names, but that does not seem like the simplest way.

I am using Hibernate, so any Hibernate-specific tricks would be nice. This seems like a problem that affects all RDBMSes in general though.

Answer 1

In Sql Server you could update them both at the same time:

UPDATE table
SET Name = CASE WHEN ID = 1 THEN 'bar' ELSE 'foo' END
WHERE ID IN ( 1, 2 )

Answer 2

In SQL Server and Oracle, constraints are deferred, that's why you just issue this query:

UPDATE  mytable
SET     name = CASE name WHEN 'foo' THEN 'bar' ELSE 'foo' END
WHERE   name IN ('foo', 'bar')

As suggested in comments, it seems that you are using this in MySQL to maintain an ordered list (that was last time I encountered this problem).

In this case you may want to read the series of articles in my blog on how to do it in MySQL more efficiently:

• Sorting lists