question

Pranzo avatar image
Pranzo asked

How to update a second column is sequence base on first column

Hi I cannot find a solution after searching for a while. My problem is Say i have a table called CallLog CREATE TABLE CallLog(ClID INT IDENTITY(1,1), Client INT, Sequence INT) What i am trying to achieve is if i insert an entry the column client will be updated but in the backed the sequence column must increment based on the client number entered So if i inserted :INSERT INTO CallLog SELECT 100 My Output should be ClID | Client | Sequence 1 | 100 | 1 If i insert the same value again my output should be : INSERT INTO CallLog SELECT 100 ClID | Client | Sequence 1 | 100 | 1 1 | 100 | 2 but if i change the Client to : INSERT INTO CallLog SELECT 101 My Output should be ClID | Client | Sequence 1 | 100 | 1 1 | 100 | 2 1 | 101 | 1 It has to be this way as i need information based on the sequence that already exists in the table. I will be inserted large amounts of data into the table aswell(60 thousand records + at a time) How do i get the column sequence to update in that manner?
sql 2008 r2
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How to update a second column in sequence based on first column... that`s what i meant!
0 Likes 0 ·

1 Answer

·
@SQLShark avatar image
@SQLShark answered
Firstly I hate triggers. You could run an update after your import to give you this or you could use a trigger. CREATE TABLE CallLog(ClID INT IDENTITY(1,1), Client INT, Sequence INT ); GO CREATE TRIGGER SequenceNo ON CallLog AFTER INSERT AS ; WITH SEQ AS (SELECT ClID, ROW_NUMBER() OVER(PARTITION BY Client ORDER BY ClID) ranked FROM dbo.CallLog) UPDATE C SET Sequence = ranked FROM dbo.CallLog C INNER JOIN SEQ ON SEQ.ClID = C.ClID WHERE c.Sequence IS NULL ; GO INSERT INTO CallLog (Client ) VALUES (100) INSERT INTO CallLog (Client ) VALUES (100) INSERT INTO CallLog (Client ) VALUES (101) INSERT INTO CallLog (Client ) VALUES (101) INSERT INTO CallLog (Client ) VALUES (101) INSERT INTO CallLog (Client ) VALUES (101) SELECT * FROM CallLog DROP TRIGGER SequenceNo; DROP TABLE dbo.CallLog;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.