3

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 the row with id=2 to foo, and the row with id=1 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.

  • 2
    I can't help but wonder why you would need something like that, though. Are you trying to maintain some sort of ordered list? If so, you're better off adding a separate "element_index" column. Can you explain what you're trying to do? – ChssPly76 Aug 07 '09 at 18:02
  • Which DBMS are you using? –  Mar 29 '16 at 17:34

2 Answers2

5

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 )
David
  • 34,223
  • 3
  • 62
  • 80
  • +1, I didn't think this would work, but I tried it out (created a table, constraint, etc) and it does work – KM. Aug 07 '09 at 18:02
4

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:

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • +1. Hibernate supports CASE statement in HQL: http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html#queryhql-expressions – ChssPly76 Aug 07 '09 at 18:01