Rita writes "I have a rather perplexing problem. I'm running SQL 7.0 Enterprise Manager on Windows NT 4.0.The end result of a COMMENT table should look like this: PPOID SequenceNumber Comment ----- -------------- ------- ABC 1 This is comment1 for ABC ABC 2 This is comment2 for ABC ABC 3 This is comment3 for ABC DEF 1 This is comment1 for DEF GHI 1 This is comment1 for GHI GHI 2 This is comment2 for GHI
To achieve this result, I have multiple INSERT INTO COMMENT statements.My problem is the SequenceNumber column. This column always has to start at "1" when the PPOID changes. I thought about creating a temp table with no SequenceNumbers but a generated number column as I need the comments in the order that they were written to the temp table. I'd then do an UPDATE on the temp table to get the correct SequenceNumber.The #TEMP table would look like this: PPOID GeneratedNumber Comment ----- -------------- ------- ABC 1 This is comment1 for ABC ABC 2 This is comment2 for ABC ABC 3 This is comment3 for ABC DEF 4 This is comment1 for DEF GHI 5 This is comment1 for GHI GHI 6 This is comment2 for GHI
Using an UPDATE statement and the generated sequence number to keep the COMMENTS in the same order, how could I change the SequenceNumbers, always starting with "1" on a PPOID change?"
Updating A Sequence Number, Always Starting With 1 On Key Change 
Posted - 2001-11-05 : 00:12:05