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?
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;
16 People are following this question.
