-1

I am trying to update the SeqNum column value based on the StudNo, StudentName. There will be sequence which should be incremented for every same set of StudNo, StudentName.

create table #student (
   StudNo int,
   StudentName char(50),
   SeqNum  int default 1, - newly added column
   StudAddress char(50)
)

Before have the seqNum(Before update data):

StudNo StudentName  StudAddress
1         Ravi        C
2         Ram         XYZ
1         Ravi        A
1         Ravi        B
3         Ram         HJK

Below is the output data which we need to get(After update):

StudNo StudentName SeqNum StudAddress
1         Ravi       1     C
2         Ram        1     XYZ
1         Ravi       2     A
1         Ravi       3     B
3         Ram        1     HJK

In the above output data we have StudNo 1 and StudentName 'Ravi' in the 1st row, 3rd row, 4th row. So the SeqNum column value is 1, 2,3 respectively.

Below is the updated query I have which will not update as expected.

update Student set seqNum=seq+1 where StudNo in (select StudNo from Student group by StudNo,StudentName having count(*) > 1)
and StudentName in (select StudentName from Student group by StudNo,StudentName having count(*) > 1)

Note: We can get output data order StudNo and StudAddress

Could anyone give me a thought on how to write update query to increment the SeqNum column values based on the set of other columns.

Thanks in Advance

M.S.Naidu
  • 2,239
  • 5
  • 32
  • 56
  • please update the question to include the before-update data; also, please provide details on how to order the data (eg, by `StudAddress`?) in order to get the 'right' `SeqNum`'s (ie, how do you know which row should have `SeqNum` =1, or =2, or =3)?; and lastly, what T-SQL code have you tried so far (and what is the 'wrong' output it is generating)? – markp-fuso Nov 20 '20 at 15:49
  • Hi @markp-fuso, Please look into the above updated post – M.S.Naidu Nov 20 '20 at 17:32
  • based on your desired output it looks like there is no specific ordering of data (eg, for `Ravi`) when assigning the `SeqNum` values (eg, there is no ordering for `StudAddress` since `ABC` == 1, `UXC` == 2, `JKL` == 3) – markp-fuso Nov 20 '20 at 17:37
  • Here I have provided the sample data, real data will be in the proper order – M.S.Naidu Nov 20 '20 at 17:39
  • Since `StudAddress` does not appear to be in any particular order, there is no way ... programmatically ... to ensure `SeqNums` are assigned such that `ABC` == 1, `UXC` == 2, `JKL` == 3; and if the idea is to keep the data in the same 'order' as it currently exists in the database ... keep in mind that, generally speaking, there is no 'order' to data in a relational database unless 'order' is enforced by indexing, an 'order by' clause, or (possibly)the physical storage of individual rows – markp-fuso Nov 20 '20 at 17:45
  • Please treat this as a sample data and that will be in the proper order in the real db. I have updated the sample data before and after update – M.S.Naidu Nov 20 '20 at 17:52
  • I don't think you understand, without some way to explicitly order the data there is no (easy) way to programmatically assign sequential `SeqNum`s ; also, in your sample code you have `seqNum=seq+1` but there is no `SeqNum` in the 'before' set of data ... what is in the `SeqNum` column in the 'before' set of data? – markp-fuso Nov 20 '20 at 17:56
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/224860/discussion-between-m-s-naidu-and-markp-fuso). – M.S.Naidu Nov 20 '20 at 17:58

1 Answers1

0

Since this is Sybase ASE we don't have access to some features (eg, windows functions, row_number(), CTEs) that exist in other RDBMS products, so we'll have to rely on some very basic SQL to update the SeqNum ...

OP has stated the SeqNum column was recently added to the table so I'm assuming the history of the table looks something like:

create table Student
(StudNo         int
,StudentName    char(50)
,StudAddress    char(50)
)
go

insert Student values (1,'Ravi','C')
insert Student values (2,'Ram' ,'XYZ')
insert Student values (1,'Ravi','A')
insert Student values (1,'Ravi','B')
insert Student values (3,'Ram' ,'HJK')
go

select StudNo, StudentName, StudAddres
from Student order by 1,2,3
go

 StudNo StudentName StudAddress
 ------ ----------- -----------
      1 Ravi        A
      1 Ravi        B
      1 Ravi        C
      2 Ram         XYZ
      3 Ram         HJK

alter table Student add SeqNum int default 1 null
go

select StudNo, StudentName, SeqNum, StudAddress
from Student order by 1,2,4
go

 StudNo StudentName SeqNum StudAddress
 ------ ----------- ------ -----------
      1 Ravi          NULL A
      1 Ravi          NULL B
      1 Ravi          NULL C
      2 Ram           NULL XYZ
      3 Ram           NULL HJK

Based on the OPs desired results we can see:

  • SeqNum should start with 1 and get incremented by 1 for each row with the same StudNo + StudentName pair
  • SeqNum should be reset to 1 when we come across a new StudNo + StudentName pair

Op has not provided any directive on how to ascertain which rows (with the same StudNo + StudentName pair) should be numbered with which SeqNum values so for this answer I'm going to assume StudAddress is unique (for a given StudNo + StudentName pair); from here I'll use the ordering of StudAddress (s2.StudAddress < s1.StudAddress) to generate a unique SeqNum for each StudNo + StudentName pair.

update Student
set    SeqNum = (select count(*) + 1
                 from   Student s2
                 where  s2.StudNo       = s1.StudNo
                 and    s2.StudentName  = s1.StudentName
                 and    s2.StudAddress  < s1.StudAddress)
from   Student s1
go

Which leaves us with the following:

select StudNo, StudentName, SeqNum, StudAddress
from Student order by 1,2,4
go

 StudNo StudentName SeqNum StudAddress
 ------ ----------- ------ -----------
      1 Ravi             1 A
      1 Ravi             2 B
      1 Ravi             3 C
      2 Ram              1 XYZ
      3 Ram              1 HJK

NOTE: Above code verified on Sybase ASE 15.7 SP140

markp-fuso
  • 28,790
  • 4
  • 16
  • 36