Jump to content

changing the position of records


KyleBragger
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 1 year later...
  • 1 year later...

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--]

 

 

 

 

 

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.