Everybody! This is important. In a few days, these forums will be moving over to using the totally sweet Discourse platform. To ensure this migration happens smoothly with no loss of content, these forums are currently in a read-only mode. I do apologize for the inconvenience.

There is never a good time to turn the forums off for an extended period of time, but I promise the new forums will be a billion times better. I'm pretty sure of it.

See you all on the other side in a few days, and if you have any (non-technical) questions, please e-mail me at kirupa@kirupa.com. For technical questions, try to find a tutorial that corresponds to what you are looking for and post in the comments section of that page.


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 kirupa.com Meet the Moderators Advertise

 Link to Us


Copyright 1999 - 2012