emulate autonumber conditionally ?? 2

[link9]

Hey all --

Here's the thing:

I have a table with two fields in it:
cd --
buf7 --

buf7 is a number that repeats for many records before it increments (it identifies accounts, and accounts have several units, which share buf7 as an identifier) --

What I need is a sql command that will go through the table and increment a number in the cd column only when the buf7 number changes. Basically, it's like an autonumber, but changes only when the buf7 does.

So, If I now have:
cd buf7
1234567
1234567
1234567
1234568
1234568

I want the command to make it:
cd buf7
1 1234567
1 1234567
1 1234567
2 1234568
2 1234568

And before you say to run an

UPDATE table SET cd=1 WHERE buf7=1234567

Let me say that there are thousands of unique buf7's, so this method would be horribly inefficient (not to mention boring).

Thanks for any help!
Paul Prewett

 

[THoey]

Paul,

Would something like this work? I didn't test it, but I don't see why it wouldn't:

-- Create Temp Table
CREATE TABLE TempTable 
(
   Buf7      VARCHAR2 (10)
   Counter   Number,
);

-- Create Sequence
CREATE SEQUENCE MySeq;

-- Load Data
INSERT INTO TempTable (Buf7, Counter)
VALUES
(
   SELECT DISTINCT Buf7, MySeq.NextVal
   FROM Mytable 
);

-- Update MyTable
UPDATE MyTable
SET Buf7 = 
(
   SELECT Counter 
   FROM Temp_Table
   WHERE MyTable.Buf7 = TempTable.Buf7
)

Make sure you remove your temp table and sequence when you are done. Hope that helps...
Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[MalcolmW]

A SQL Server solution is a bit more convoluted, but similar:

-- Create temp table
Create table #SeqTable (Buf7 int, SeqNum int)
-- Populate temp table with distinct Buf7 values
INSERT INTO #SeqTable (Buf7, SeqNum)
   SELECT DISTINCT buf7, NULL
   FROM #PaulsTable
-- Assign sequence numbers to the Buf7 values
  declare @variable int
  set @variable = 0
  update #SeqTable
  SET @variable = SeqNum = @variable + 1 
-- Update your table with the sequence numbers
  update #PaulsTable 
  Set cd = (
           select SeqNum 
           from #SeqTable 
           where #SeqTable.Buf7 = #PaulsTable.Buf7
           ) Malcolm Wynden
I'm for sale at [URL unfurl="true"]http://wynden.net[/URL]
malcolm@wynden.net

 

[link9]

See why this site is so grand?

Excellent, guys. Thanks to both of you.

paul