Swapping keyed values

Databases
1

Streamlining question…

Is there a way to swap two numeric values that are keyed (unique) in a single update statement? The only way i can think to do it is with three (update the fields to a non-existant key-pair, and then update each individually to put it back in the correct slot)

2

You’ve got the idea… but your code doesnt work.
Using a different table for safety, i did this:

UPDATE games
SET id = CASE WHEN id=2 THEN 3
WHEN id=3 THEN 2
ELSE id END
WHERE id IN (2,3)

And MySQL belched at me
#1062 - Duplicate entry ‘3’ for key 1

3

looks like you might have to use more than one update statement

4

hey that’s pretty inventive – nice job!! :slight_smile:

5

I eventually struck upon the thought that i could do it in two using the set… case… syntax…because id is a Signed Int field that’s only using it’s positive values.

#1 = UPDATE games SET id = id * -1 WHERE id IN($a,$b);
#2 = UPDATE games SET id = CASE WHEN id = ($a * -1) THEN $b WHEN id = ($b * -1) THEN $a ELSE id END WHERE id IN ($a,$b);

6

Assume a populated table with unique-keyed INT field 1…100

Swap key values 2 and 3.
R1 = 2 (10)
R2 = 3 (11)
R1 = R1 XOR R2 = 10 X 11 = 01 = 1 = collision. Failed operation.

7

A wonderful theory, but i dont see how that solves my problem.

If you tell it to apply transformation on 2 rows, it will apply it to one row at a time - causing a key collision.

The cited theory uses 3 operations, and is therefore not more efficient than the former (and in fact is worse, if R1 XOR R2 exists as a key already - cause it’ll collide then)

8

check this out – http://en.wikipedia.org/wiki/Swap_by_addition_and_subtraction

9

ah, it makes a bit more sense now

in your first post, i got the impression you were trying to swap the values of two columns in the same row (you mention “key-pair”)

are you trying to swap two values of a key column on separate rows?

do you know the values? if, for example, 187 and 937 have to be swapped, you could do it like this –

UPDATE daTable
   SET col = CASE WHEN col=187 THEN 937
                  WHEN col=937 THEN 187
                  ELSE col END
 WHERE col IN (187,937)

if this doesn’t cover it, then i don’t know what you’re asking