UPDATE query on a unique field

[Dick Watson]

Given a table tblTest with two long integer fields Parent and Seq, where
Parent and Seq, combined, are defined as a unique index, I want to do
something like:

UPDATE tblTest SET Seq = Seq+1 WHERE (Seq>=2 AND Parent=1);

Example desired result:

WAS
Parent Seq
0 1
1 0
1 1
1 2
1 3
2 0

NOW
Parent Seq
0 1
1 0
1 1
1 3
1 4
2 0

(I want to insert an opening in the Seq for a new sequence number.)

But I get key violations when running the query, presumably because it can't
update 2 to 3 because there's already a 3 there.

What's the best way to accomplish what I want here?

Thanks for your help!

 

[Tom Ellison]

Dear Dick:

It's like the guy painting the floor. He starts at the doorway, and paints
himself into the corner.

Is that you you feel?

I actually found a way to "insert" things in an order like this. Simple
application concept, difficult to implement.

For the basic concept, consider this. If you numbered the Seq initially
1000, 2000, 3000, 4000, etc. then you'd have "room" to insert new values
between. Split the difference. To insert between 1000 and 2000 you use
1500. Between 1000 and 1500, use 1250. You have to insert many times
before you would run out of room to insert again.

What I actually did was to use an 80 character string to store these
"numbers" but the numbers weren't just base 10. I used base 32, using
digits 0-9 then the letters A-V. By using a power of 2, splitting the
difference became easy.

So, whenever a new sequence number was added at the high end, I just used
the first 4 characters of the 80. The rows would be numbered 0001, 0002,
0003 up to 0009, then 000A, 000B.

Now, when I say 0001, it is really 00010000000 . . .

Out to 80 places.

Inserting between 0001 and 0002 you would just use 0001G. Between 0001 and
0001G would be 00018. Then successively, 00014, 00012, 00011, 00011G, and
so on. Each of the remaining 76 characters of the string could be divided
at the same point 5 times. Using 76 characters then, you could insert a new
row immediately after 0001 a total of 380 times before it would fail. No
one ever came close to doing that. So, that's how I can number the rows of
an invoice, allowing the user to control that order and keeping the invoice
in the desired order, and implement insertions.

Well, your problem reminds me very much of that. Can you see why? Would
such a solution be useful?

I can even tell you what to do on the 381st insertion and keep it going for
a long, long time! It will take a few seconds, you can bet, but I would
copy the whole list out of the database, renumber from scratch (0001, 0002,
0003 . . .) and put them back. I could even give you a plan to recover if
the computer crashes while doing this.

If anybody has a good alternative, I'd love to hear it!

You've pried one of my secrets from me. Actually, this shouldn't be so
difficult. It should probably be implemented inside the capabilities of a
database somewhere, rather than be dumped on us poor application
programmers! Well, don't hold your breath!

Tom Ellison

 

[giorgio rancati]

Hi Dick,
try this
----
UPDATE (SELECT *
FROM tblTest
WHERE (Seq>=2 AND Parent=1)
ORDER BY Seq Desc) AS T
SET Seq = Seq+1

 

[Dick Watson]

Intuitively I like both of those. I'll go test them.

 

[Dick Watson]

I used to use a single here instead of a long. Inserting was EASY. But
having a procedure (these are step numbers) with steps like
(4.655).(3.1).(2.95) was really a lot less than ideal. So I covered that in
the reports by using a counter in the detail with an unbound control set =1
but set with a running sum on each detail instead of using the stored
sequence value. That was for a two level structure. (Step 2.1.) Now I want
to go arbitrarily deeper. (Step 1.3.4.7.9.) This makes both reporting and
the UI for updating steps seen in the report with a certain number a lot
more difficult. Your approach seems to suffer the same problem--it's easy
for the database to grind on but not for the table user to touch and feel.

Thanks anyway for the thoughts--it's a useful technique to think through and
keep in the brain.

It's like the guy painting the floor. He starts at the doorway, and
paints himself into the corner.

Is that you you feel?

Indeed...

 

[Tom Ellison]

Dear Dick:

The method I gave was definitely not intended for user "touch and feel". It
is meant to be totally hidden.

I did not no that you wanted to have the user see what is being stored to
order the entries.

What you are suggesting is a "Dewey Decimal" approach. This can also be
implemented fairly nicely.

I suggest for this that you determine in advance the maximum number of
digits allowed for each segment. That is, are you going to allow 1.99.99.99
or 1.999.999.999?

The value stored, so it will sort properly and easily, would be like this.
For a 2 digit segment maximum, 1.3.4.7.9 would be stored 0103040709. Stored
as a string it would then sort porperly.

Write functions to take an entered value and convert to this format, and to
go from stored to displayed values. I would write it to work on a
specification for the setment format, like "22222" meaning 2 digits for the
first segment, 2 for the second, and so on. Using a specification like this
gives you flexibility. You could even change specifications, making one or
more segments larger or smaller, and converting between them.

Tom Ellison