Swapping data in mysql

netfrugal

Is there a function in mysql that allows me to swap two numbers from different rows?

Situation:

I wish to change sort orders for my rows with up and down arrows.

Naturally, I can SORT ORDER recID ASC and get my numbers showing from 1 to 10 in numeric order.

But, I may want to replace number 3 with 4.

So, without having to go into the database and manually change the recIDs, I'd like to swap the numbers with my PHP, or use a mysql function that swaps numbers.

Unless there's an easier way to do it, I'm all ears!

Piranha

Just run 2 queries:

UPDATE table SET number = number + 1 WHERE id = 14;
UPDATE table SET number = number - 1 WHERE id = 34;

netfrugal

Yes, that's what I thought too, but there's a problem with that,

if I run the first update statement and it changes the number from 14 to 15, there may already be a number 15 . Then I may have a sorting problem between 2 of the same numbers.

am I explaining it clearly enough, because it hurts my head to even think about it! lol

MarkR

If you want to swap two numbered rows, then you could consider:

 $a = 42;
 $b = 69;
 $sum = $a + $b;
 $sql = "UPDATE items SET priority = $sum - priority WHERE priority IN ($a, $b)";

You can hopefully see how / why this would work. It would fail if other rows had the same priority (well, it would swap them too)

Mark

Piranha

Nice one Mark 😃