I have a table as follows
ID NAME SeqNo
1 aaa 0
2 bbb 0
1 aaa 1
1 aaa 2
2 bbb 1
Upon inserting a new record into the table with id already existing in the Table
The Sequence number should be incremented by 1.
Ex: upon adding this row
sequence number now should be
2 bbb 2. (just like incrementing the seq no as an identity column.)
Can i do it like this.
tempvar = select Max(seqno) where id = @id.
and doing TempVar + 1 on inserting will increment the column .
Please advice if there is any better approach to do this.