3

I took a look at the question: sql swap primary key values

So I got the following script:

UPDATE t, t as t2
SET t.id = t2.id, t2.id = t.id
WHERE t.id = 1 AND t2.id = 2

But i can not translate this to a valid SQL Server syntax.

Please help me :)

Thanks in advance!

Community
  • 1
  • 1
HerbalMart
  • 1,669
  • 3
  • 27
  • 50

2 Answers2

5

What about

UPDATE t SET t.id = (CASE t.id WHEN 1 THEN 2 ELSE 1 END)
WHERE t.id IN (1, 2)
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
5

It looks like you are trying to join the table against itself so that you can update two records at once. I'm not sure if it's possible, but it's certainly not needed.

Just locate the two records, and calculate one id from the other by subtracting it from the sum of the two ids:

update t
set id = (1 + 2) - id
where id = 1 or id = 2

(Note: This requires that the key is numeric. While this is the most common case, some non-numeric types can also be used as keys.)

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • @Lukas Eder: I think that it's pretty much as readable as you can get it. The only tricky part is the mathematics used, where `(x + y) - x = y`, once you wrap your head around that, it should be pretty straight forward. – Guffa Jul 06 '11 at 10:03
  • that's what I mean by unreadable. One hast to calculate through it to see what it actually does. For someone who's never seen this, it doesn't look like swapping. But it's nice. More concise than my idea. I'll certainly use this some time – Lukas Eder Jul 06 '11 at 10:12
  • CASE is more universal as it will work for any data type, but this method is really nice anyway! – Andriy M Jul 06 '11 at 12:17
  • @Andriy M: That is a good point, if you have an unusual type for key, a `case` would still work. – Guffa Jul 06 '11 at 12:23
  • Oh, sorry, I forgot that this question was about swapping *keys*. I was talking about general applicability. Of course, when speaking about keys, CASE is only *slightly* more universal than this method, which makes the latter even more attractive. :) – Andriy M Jul 06 '11 at 12:35