Swap Id?

[tomthinks]

Hi. I'm new to mySQL and I'm trying to make two rows in a table swap ids (ultimately swapping positions). I'm not sure how to go about it and haven't found anything on it yet. Any help would be greatly appreciated.

Thanks.

-Tom

[bwh2]

i don't really know if there's a super clean way. for the time being, i know this will work:


/* assumes you started your ID column at 1, not 0 */
SET @tmp_id = 0;

/* first ID you want to swap */
SET @first_id = 2;

/* second ID to swap */
SET @second_id = 3;

/* set first ID to 0 */
UPDATE tblName
SET idColName = @tmp_id
WHERE idColName = @first_id;

/* set second ID to first ID */
UPDATE tblName
SET idColName = @first_id
WHERE idColName = @second_id;

/* set first ID to second ID */
UPDATE tblName
SET idColName = @second_id
WHERE idColName = @tmp_id;why would you want to do this? i mean, the ID field isn't really for order. it's just a unique identifier for a row of data.

[tomthinks]

I'm working on a content management system where you can rearrange the order of individual entries in a list. I don't mean asc, desc, but moving individual entries up and down.

[bwh2]

if you're changing the order, you should just have an order column. you should never be changing the ID. the purpose of having the ID is to use it as a foreign key in other tables. if you start changing the ID in the primary table, your relationships will break down.

[tomthinks]

Thank you, how does one order a column? I hadn't seen that function in the tutorials I've taken. Thanks a lot!

[bwh2]

basically just add a column. i would recommend naming it something like `rowOrder` and give it a datatype of INT, with UNIQUE on. that way, your order values will always be unique.

then rowOrder will be the column you UPDATE when the user is changing the presentation order. to initially populate the column, i would run this:


UPDATE tblName SET rowOrder = idColName

that will start set all of your rowOrder values to the current ID values. from there, when you insert a new record into the DB, you will want to UPDATE the rowOrder value for that entry to equal the ID number. thus, a new record would intially drop to the bottom of the order.

[tomthinks]

Ok, I get what you're saying now. The only thing I'm not sure about is how to write the UPDATE code so that it will change the rowOrder fields of the two entries that are being swapped.My table is called "events".



UPDATE events rowOrder='$b' where id= '$id'
UPDATE events rowOrder='$a' where id='$idb'


how do i make it update both in one query?

[borrob]

this will not work if you're column is unique,

because after the first update there would be two rows with the same number.

so rather do:

!notice this is an example not exact code

SELECT roworder from events where id= '$id'
$temp_swap1 = result
SELECT roworder from events where id= '$idb'
$temp_swap2 = result

UPDATE events set( rowOrder ) values( -1 ) where id= '$id'
UPDATE events set( rowOrder ) values( '$temp_swap1' ) where id='$idb'
UPDATE events set( rowOrder ) values( '$temp_swap2' ) where id= '$id'

i don't think you can do it in a single statement.

oh and remember to update your roworder when deleting or inserting a record

succes