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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » here i just want update the indexnumber of...

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

Rate Topic

Display Mode

Topic Options

Author

Message

sivajii

Posted Monday, October 22, 2012 6:04 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:22 AM
Points: 211, Visits: 1,327

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 questionbank	indexnumber question	createddate
1	B7152F93-45CE	1	     q1	       2012-10-22 04:10:19.457
2	B7152F93-45CE	2	     q2	       2012-10-22 04:10:19.457
3	B7152F93-45CE	3	     q3	       2012-10-22 04:10:19.457
4	B7152F93-45CE	4	     q4	       2012-10-22 04:10:19.457
5	B7152F93-45CE	5	     q5	       2012-10-22 04:10:19.457
6	AB2B400C-CE03	1	     q1	       2012-10-22 04:10:19.457
7	AB2B400C-CE03	2	     q2	       2012-10-22 04:10:19.457
8	AB2B400C-CE03	3	     q3	       2012-10-22 04:10:19.457
9	AB2B400C-CE03	4	     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 questionbank	indexnumber	question	crdate
1	   B7152F93-45CE	1	q1	2012-10-22 04:10:19.457
3	   B7152F93-45CE	3	q3	2012-10-22 04:10:19.457
4	   B7152F93-45CE	4	q4	2012-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 questionbank	indexnumber	question	crdate
1	   B7152F93-45CE	1	q1	2012-10-22 04:10:19.457
3	   B7152F93-45CE	2	q3	2012-10-22 04:10:19.457
4	   B7152F93-45CE	3	q4	2012-10-22 04:10:19.457

can anyone plz try to solve this one

Post #1375362

ChrisM@home

Posted Monday, October 22, 2012 6:21 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 921, Visits: 3,813

-- 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

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

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

Post #1375373

sivajii

Posted Monday, October 22, 2012 6:51 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:22 AM
Points: 211, Visits: 1,327

thanks ChrisM@home
it was working fine

Post #1375399

« Prev Topic | Next Topic »

Permissions