In the example you give, you could do the changes with two UPDATE
commands:
1) UPDATE questions SET order=0 WHERE order=5;
2) UPDATE questions SET order=order+1 WHERE order<5;
It becomes more tricky when you try and move a question to a position
other than the first one (e.g. question #6 to move to position #3 and all
other questions to be shifted accordingly).
This would take three UPDATEs:
1) UPDATE questions SET order=0 WHERE order=6;
2) UPDATE questions SET order=order+1 WHERE order>=3 and order<6;
3) UPDATE questions SET order=3 WHERE order=0;
Perhaps other posters have more efficient or shorther methods?
Two things to bear in mind:
a) You may experience problems with UPDATEs of the 'SET order=order+1'
type if you have declared a unique index on order
b) I think order is a reserved keyword (it's used in ORDER BY clauses) so
you may want to rename that field.
HTH,
Regards,
Stuart.
On Wed, 27 Oct 1999, Shawn T. Walker wrote:
> Hi All,
>
> I have a table of questions to be displayed to the user.
>
> Table: questions
> question_id SERIAL
> page int
> order int
> question varchar(100)
> answers text
>
> The field order is how the questions are sorted. How can I change change
> question 5 to be number one and have all the other questions re-order
> themselves?
>
> Thanks in advance.
> Shawn
>
> -------
> Shawn T. Walker <---> shawn@netcrafters.com
>
>
>
>
>
> ************
>
Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: rison@biochem.ucl.ac.uk