The forums have permanently moved to This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.

Results 1 to 8 of 8

Thread: Swap Id?

  1. #1

    Swap Id?

    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.



  2. #2
    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.

  3. #3
    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.

  4. #4
    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.

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

  6. #6
    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.

  7. #7
    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?

  8. #8
    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

    Last edited by borrob; February 15th, 2007 at 08:15 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Home About Meet the Moderators Advertise

 Link to Us


Copyright 1999 - 2012