Update row value swap help

[rayhab]

Hi, I'm working in mySQL
Here is the type of update i need

Before Statement:
Table A
|col1| col2| col3
-----------------
1 |row1| row1
2 |row2| row2
3 |row3| row3

After Statement:
Table A
|col1| col2| col3
-----------------
1 |row1| row1
3 |row2| row2
2 |row3| row3

Any optimized ideas would be appreciated.

[TJ111]

Call me crazy, but both those examples look identical to me.

[rayhab]

notice the value swap in the second and third row only the first column has been changed

[TJ111]

Are you trying to move the values of a row already in a table to a different place in the table? Are you trying to insert data into table at a specific point? Whats the purpose, is it being displayed on a webpage or similar? Depending on what your doing exactly this could be done many ways, some alot easier then others.

[rayhab]

it's pretty much the user is editing the sequence number of the first column..

So the user is on the page with these rows listed and edits the Id number of the first columns.
So these rows are already entered in the database. The user is simply re-arranging the sequence order.

[TJ111]

You could create a column 'customIndex' and allow user's to manipulate those numbers, then reenter them into the table. Then when you are pulling the information out of the database add an 'ORDER BY' to the end of your query.


$qry = "SELECT * FROM table_name ORDER BY customIndex";

[rayhab]

Thanks for the help. Was able to get it working off your suggestion.