Re: Updating the SQL key value

Erwin Moller wrote:
bill wrote:

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

Aha. I see now what you mean.
Hmm, that is tricky to do with 1 UPDATE statement since you didn't use a 'normal' autonumbering Primary Key.


I think you have to resort to a simple script to do that for you.

Can we assume that you NEVER have any double values for 'order'?
I mean, did you make that field PK or at least UNIQUE? Or as a last resort, check before inserting that the 'order' didn't exist yet?

If they are all UNIQUE try something like:
-- pseudocode ADODB-like, adjust to your own prefered databaselogic

$SQL = "SELECT order FROM tblorder ORDER BY order";
$result = $connection->Execute($SQL)->getArray();

$count = 10;
foreach ($result as $oneOrder){
$oldOrderNum = $oneOrder["order"];
// Update
$SQL = "UPDATE tblorder SET order=".$count;
$SQL .= " WHERE (order=".$oldOrderNum.");";
$connection->Execute($SQL);
}




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.

In this case: maybe.

But take it from an old db-fart like me: Start using an autonumbering PK for each table you create from now on, unless you have a compelling reason not to. I actually was never in a situation where an autonumbering PK hurts.
Only in some cases you really know it doesn't make sense (scratch tables, temp tables, etc).
As a rule of thumb: Use autonumbering PK on every table.

In my above example I wouldn't need to be afraid you have some values for 'order' that are the same (which would make that approach fail). A autonumbering PK would have eliminated that problem.

Understood. I will add a autonumbering PK


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

Unless you mean my above script. That takes the all 'order' values from tblorder in memory, but that is independent from the database, it is PHP's memory.

Works for me.
Thanks

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

Not if you read them all at once first into memory of php and start updating then.

bill
Regards,
Erwin Moller
your assistance is appreciated


You're welcome

bill
Regards,
Erwin Moller

I certainly appreciate the tutorial. I understand the internals of mySQL just a little bit better.

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: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Updating the SQL key value
    ... before creating the database, by the time I've come to pick a primary key, ... articles, each of which must be issued under a particular licence (e.g. ... GPL GNU General Public Licence http://www.gnu ... ...
    (comp.lang.php)
  • Re: Concatenate and Null Values -- Features
    ... Tony Toews dislikes cascade deletes as well as cascade updates, ... fence regarding the use of natural versus surrogate (autonumber) keys. ... Database Normalization Tips ... For optimal database design and performance, the primary key of a table ...
    (microsoft.public.access.reports)
  • Re: Option groups, radio buttons and reports...oh my!!
    ... designing a 'questionnaire' database of using separate columns for the ... referencing the primary key of the Questions and Answers table. ... composite primary key of QuestionAnswers. ... a composite one of RespondentID and QuestionID; ...
    (microsoft.public.access.gettingstarted)