Re: Updating the SQL key value

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
.

Relevant Pages

  • Re: Updating the SQL key value
    ... field "order" as the primary key. ... 'normal' autonumbering Primary Key. ... I never heard of a database that changes the values of other rows if you ... tblorder in memory, but that is independent from the database, it is PHP's ...
    (comp.lang.php)
  • Re: Upsizing
    ... Just had a look and each table does have a primary key. ... "Sylvain Lafontaine" wrote: ... > Queries window. ... >>I have upsized an Access database to an SQL database via the Access ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Q: Datatables, Datasets and updating
    ... I have two tables A and B in the SQL database. ... ID_A (the primary key of A) and NAME_A (a string ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: dbdebunk Quote of Week comment
    ... > we need an element in our model that will denote an end-user ... > althoug it is as important as data modeling itself. ... > from different tables - having one primary key is not enough). ... > Memory handle is really physical for an application program that uses ...
    (comp.databases.theory)
  • Re: Updating the SQL key value
    ... bill wrote: ... I save them in a sql database using the field "order" (a smallint) as the primary key. ...
    (comp.lang.php)