Posted by Erwin Moller on 03/28/07 15:41

bill wrote:

Hi Bill,

> In an application I am writing the user can define a series of
> steps to be followed. I save them in a sql database using the
> field "order" (a smallint) as the primary key.
> (there are in the range of 20 steps)
>
> On the admin page the steps are listed, in "order" order and the
> user can create new steps and assign an order and all is well.
>
> The problem may come in using a renumber function which should
> take the steps in their current order and reassign the "order"
> key, assigning each set an "order" that is the prior "order" + 10.

Well, what about:

UPDATE tblwhatever set order = order+10;


>
> In other databases I have worked with this is a major pain
> because as soon as you save a record the order may change.

I never heard of a database that changes the values of other rows if you
insert a new row.
Are you maybe using this order filed as Primary Key?

If so: That is a major designmistake.
(I'll elaborate if this turns out to be the case.)

> OTOH, in mysql it appears that if you do a select for the whole
> table you have them all in memory and can change the value of the
> field "order" without having to worry about getting that record
> again and then save the whole table.
>
> Is that correct ?

No.

A query is a query: something that return results (or none).
If you SELECT something, nothing is changed.

I don't understand what you mean by 'selecting the whole table in memory'.
If you need to UPDATE certain records, use the UPDATE command to do so.

>
> bill

Regards,
Erwin Moller

[Back to original message]