changing the position of records

[KyleBragger]

Okay, how would I change the position of records in a table, e.g.:

 

id name

1 A

2 B

3 C

 

...and I want to move \'B\' down in the list...

 

id name

1 A

2 C

3 B

 

Please note that ORDER BY is not what I am asking about, as I would like this to be permanant and done within the table, as opposed to an output-only type of thing.

[ystrigathe]

because updates & join don\'t really work together, i think i\'d like to

 

alter table xyz add new_id int default null;

update xyz set new_id = ... // changing positions

 

update table xyz set id = id * (-1) where new_id is not null; // change values of keys (i think, id is primary key or at least unique)

update table xyz set id = new_id where new_id is not null; // only update for changed records...

[dkittell]

Is there a simple tutorial out there on this?

I knew how to do it in ASP but I work with PHP now and the code I used won't completly work

 

Any help is appreciated.

[ober]

To the origional poster, you're going to have to move them with a series of SELECTs and UPDATEs.

 

If you want to switch two:

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?

$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT name FROM table1 WHERE id IN (2,3)\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#FF8000\"]// get the results

[/span][span style=\"color:#0000BB\"]?>

[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?

$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"UPDATE table1 SET name = $row[0] WHERE id = 3\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#FF8000\"]// execute

 

[/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"UPDATE table1 SET name = $row[1] WHERE id = 2\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#FF8000\"]// execute

[/span][span style=\"color:#0000BB\"]?>

[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]