here i just want update the indexnumber of a question table after deleting some question from that table ?

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  October 22, 2012 at 6:04 am

  #263600

  here i am having table @question which is having foreign key questionbank datatype uniqueidentifier

  this is the table

  DECLARE @question table

  (

  questionid int identity(1,1),

  questionbank uniqueidentifier,

  indexnumber int,

  question varchar(20),

  crdate datetime

  )

  insert into @question

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',1,'q1',GETDATE() union all

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',2,'q2',GETDATE() union all

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',3,'q3',GETDATE() union all

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',4,'q4',GETDATE() union all

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',5,'q5',GETDATE() union all

  select 'AB2B400C-CE03-4233-9191-DAE3A5CBBC53',1,'q1',GETDATE() union all

  select 'AB2B400C-CE03-4233-9191-DAE3A5CBBC53',2,'q2',GETDATE() union all

  select 'AB2B400C-CE03-4233-9191-DAE3A5CBBC53',3,'q3',GETDATE() union all

  select 'AB2B400C-CE03-4233-9191-DAE3A5CBBC53',4,'q4',GETDATE()

  select * from @question

  the output will be

  questionid questionbankindexnumber questioncreateddate

  1B7152F93-45CE1 q1 2012-10-22 04:10:19.457

  2B7152F93-45CE2 q2 2012-10-22 04:10:19.457

  3B7152F93-45CE3 q3 2012-10-22 04:10:19.457

  4B7152F93-45CE4 q4 2012-10-22 04:10:19.457

  5B7152F93-45CE5 q5 2012-10-22 04:10:19.457

  6AB2B400C-CE031 q1 2012-10-22 04:10:19.457

  7AB2B400C-CE032 q2 2012-10-22 04:10:19.457

  8AB2B400C-CE033 q3 2012-10-22 04:10:19.457

  9AB2B400C-CE034 q4 2012-10-22 04:10:19.457

  here i just wrote delete store procedure by passing one parameter @DeleteId varchar(MAX)

  her i just called split function to delete mutiple questionid at a time for eg; '2,5'

  here is the procedure

  declare @DeleteId varchar(MAX)='2,5'

  declare @questionbank uniqueidentifier

  set @questionbank =(select top 1 questionbank from @question where questionid in (select val from [dbo].[FN_SplitData] (@DeleteId,',')))

  Delete from @question where questionid in (select val from [dbo].[FN_SplitData] (@DeleteId,','))

  select * from @question where questionbank=@questionbank

  which will give output like this

  questionid questionbankindexnumberquestioncrdate

  1 B7152F93-45CE1q12012-10-22 04:10:19.457

  3 B7152F93-45CE3q32012-10-22 04:10:19.457

  4 B7152F93-45CE4q42012-10-22 04:10:19.457

  and

  now i just want to change the index number in a order in this proc itself like this

  questionid questionbankindexnumberquestioncrdate

  1 B7152F93-45CE1q12012-10-22 04:10:19.457

  3 B7152F93-45CE2q32012-10-22 04:10:19.457

  4 B7152F93-45CE3q42012-10-22 04:10:19.457

  can anyone plz try to solve this one

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  October 22, 2012 at 6:21 am

  #1551330

  -- sample data setup

  DECLARE @question table

  (

  questionid int identity(1,1),

  questionbank uniqueidentifier,

  indexnumber int,

  question varchar(20),

  crdate datetime

  )

  insert into @question

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',1,'q1',GETDATE() union all

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',2,'q2',GETDATE() union all

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',3,'q3',GETDATE() union all

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',4,'q4',GETDATE() union all

  select 'B7152F93-45CE-4485-9672-7E0E5F62F775',5,'q5',GETDATE() union all

  select 'AB2B400C-CE03-4233-9191-DAE3A5CBBC53',1,'q1',GETDATE() union all

  select 'AB2B400C-CE03-4233-9191-DAE3A5CBBC53',2,'q2',GETDATE() union all

  select 'AB2B400C-CE03-4233-9191-DAE3A5CBBC53',3,'q3',GETDATE() union all

  select 'AB2B400C-CE03-4233-9191-DAE3A5CBBC53',4,'q4',GETDATE()

  -- check the sample data

  select * from @question

  -- delete a couple of rows

  DELETE FROM @question WHERE questionid in (2,5)

  -- renumber indexnumber

  ;WITH Renumberer AS (

  SELECT *,

  NewIndexNumber = ROW_NUMBER() OVER(PARTITION BY questionbank ORDER BY IndexNumber)

  FROM @question

  )

  UPDATE Renumberer

  SET indexnumber = NewIndexNumber

  WHERE indexnumber <> NewIndexNumber

  -- check that the renumber has worked

  SELECT * FROM @question

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  October 22, 2012 at 6:51 am

  #1551353

  thanks ChrisM@home

  it was working fine