Sequence Number - Re: thread 185-859372

[timb94]

I just discovered ESquared and donutman's discussion of adding a sequence number to a table using a update query instead of a cursor. The thread I reference is at thread183-859372.

In my case I need to take it one step farther and reference two columns as a key instead of one.

My data looks like this:
Field 1 Field 2
8175 1
8175 1
8175 2
8175 2
8175 2
8180 1
8180 2
8180 2

The results would be:
Field 1 Field 2 SeqNumber
8175 1 1
8175 1 2
8175 2 1
8175 2 2
8175 2 3
8180 1 1
8180 2 1
8180 2 2

I tried to retrofit there solution into my procedure but I'm having trouble with the coding and I'm not sure it can be done.

If I can solve this problem I can eliminate 3 cursors from my system.

Has anyone else tried the above with ESquared or donutman's solution or am I stuck with cursors?

Any assistance would be appreciated.

 

[timb94]

I think I found my problem. But I would appreciate any comments or suggestions.

Thanks

 

[ESquared]

Do not use an UPDATE with an ORDER BY. From the thread you referenced:

You cannot coerce a temporary table into an ORDER BY for an UPDATE statement

I have found some isolated cases where the update didn't proceed in the order I expected

INSERT or SELECT INTO with ORDER BY is okay, but not UPDATE.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[timb94]

Thanks ESquared for your reply.

One question. In your sample you have an order by statement on the inner join select statement. Is this a bad idea? In the testing I've done so far it doesn't appear to be a problem.

 

[ESquared]

Dear timb94,

I just told you it was a bad idea.

Don't trust your testing. It is a fact that you cannot rely on the order of updates, just like you cannot rely on the order of rows returned from a select (unless you use an order by clause) even if all your testing over and over is never out of order. For example, there are cases where when two processes are accessing the same index and have overlapping segments of data needed, the later one can "ride" on the earlier one to get some of its data at the same time, returning rows out of order.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[timb94]

Thanks you for your prompt reply.

I don't think I've ever used an order by in a update.

I have changed my procedure to create a ordered scratch table to use in the update statement and have dropped the order by in the update.

Better to be safe then sorry.

Thanks again for yours and donutman's replys and continued assistance.

 

[ESquared]

If you'd like help validating that it will work reliably, submit your query here.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]