Re: Updating the SQL key value

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
.

Relevant Pages

  • Re: Very slow query
    ... indexed for all the secondary tables, only the primary key is indexed). ... create a new blank database and immediately turn OFF the Name ... With 14 secondary tables or less, my query runs in 1 sec or less. ... in forms, base the form on the primary data table, and use combobox or ...
    (microsoft.public.access.queries)
  • Re: Query to merge
    ... Thanks for that insite - I am sure that the additional customers (that would ... simply build your query to give you all records from the one, ... Say it was a customer database. ... customers with the same primary key. ...
    (microsoft.public.access.queries)
  • Re: Is this bad design?
    ... application which is making heavy use of an Access database. ... design but there might be good reasons for doing things this ... Tables with a generated primary key i.e. an id where a number ... Relations can be created on the fly in the query builder and/or ...
    (comp.databases.ms-access)
  • Re: Very slow query
    ... indexed for all the secondary tables, only the primary key is indexed). ... create a new blank database and immediately turn OFF the Name ... With 14 secondary tables or less, my query runs in 1 sec or less. ... in reports, base the report on the type of query described above ...
    (microsoft.public.access.queries)
  • Re: Other options beside seek
    ... inserts into the database I am using a simple query returning the ... based upon the primary key fieldso that I do not have to change ... the index back and forth and then from my query results I can either ... I know that the RecNo property ...
    (borland.public.delphi.database.ado)