Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

here i just want update the indexnumber of a question table after deleting some question from that table ? Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 6:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 212, Visits: 1,378
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
Posted Monday, October 22, 2012 6:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 1,075, Visits: 6,441
-- 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
Posted Monday, October 22, 2012 6:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 212, Visits: 1,378
thanks ChrisM@home
it was working fine
Post #1375399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse