|
|
Posted by bill on 03/29/07 11:26
Erwin Moller wrote:
> 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;
I think I was unclear:
the order might evolve to:
10
12
16
18
30
33
and after renumbering it should be back to
10
20
30
40
50
60
>
>
>> 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.)
guilty.
That is why the updating of the "order" would result in the
problem.
If I use another primary key (an arbitrary ID) then I still have
to read the records in "order" number and reassign the value of
"order" and then read the next record.
>
>> 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.
bummer
>
> 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.
but after having UPDATEd, one still has the problem of reading
the next record
>
>> bill
>
> Regards,
> Erwin Moller
your assistance is appreciated
bill
Navigation:
[Reply to this message]
|