Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update sortorder column to +1


Update sortorder column to +1

Author
Message
kaushal_shah26
kaushal_shah26
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 30
Hi Guys,
I have a table like this

Id----------Type--------ConditionId-------isActive------SortOrder
1001-------860----------2----------------1------------1
1002-------860----------3----------------1------------0
1003-------860----------5----------------1------------Null
1004-------860----------1----------------0------------Null
1005-------860----------4----------------0------------Null


There are 3 conditions which are active. Out of those 3 conditions, 2 have sortorder value (1 and 0).

I need to write a script whereI need to update the record where condition is active but sortOrder is null (In the case above, it's conditionId 5).
I need to update the sortOrder with the next value of the maximum sortOrder for the type where isactive is 1.

so, in the case above, after the update, Id = 1003 should look lik this

1003-------860----------5----------------1------------2

I have been trying combination of queries but more I tried, more confused I got.

I would appreciate if you guys can guide me to resolve this matter.

Thanks.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024
I think I understand what you are trying to do. Can you post some ddl (create table) and sample data (insert statements) in a consumable format?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kaushal_shah26
kaushal_shah26
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 30
Thanks for the quick response. Below is the script that I used to create table and insert sample data.

CREATE TABLE [dbo].[CustomerConditions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Type] [int] NULL,
[ConditionId] [int] NULL,
[isActive] [bit] NULL,
[SortOrder] [int] NULL
);


insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,2,1,1);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,3,1,0);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,5,1,null);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,6,1,null);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,1,0,null);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,4,0,null);

select * from CustomerConditions;


-------------

Thanks.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14965 Visits: 38984
i think it's simply like this:

...ORDER BY ISNULL(SortOrder,2)



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kaushal_shah26
kaushal_shah26
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 30
kaushal_shah26 (8/1/2012)
Hi Guys,
I have a table like this

Id----------Type--------ConditionId-------isActive------SortOrder
1001-------860----------2----------------1------------1
1002-------860----------3----------------1------------0
1003-------860----------5----------------1------------Null
1004-------860----------1----------------0------------Null
1005-------860----------4----------------0------------Null


There are 3 conditions which are active. Out of those 3 conditions, 2 have sortorder value (1 and 0).

I need to write a script whereI need to update the record where condition is active but sortOrder is null (In the case above, it's conditionId 5).
I need to update the sortOrder with the next value of the maximum sortOrder for the type where isactive is 1.

so, in the case above, after the update, Id = 1003 should look lik this

1003-------860----------5----------------1------------2

I have been trying combination of queries but more I tried, more confused I got.

I would appreciate if you guys can guide me to resolve this matter.

Thanks.







Sorry, I forgot to mention that there might be case where two or more conditions are active but they do not have sort order. In the case above, If I add one more row

1006-------860----------6----------------1------------Null (conditionId = 6)
then I have two conditionId (5 & 6) that are active but sortorder is null

The final output should be

1003-------860----------5----------------1------------2
1006-------860----------6----------------1------------3


Thanks a million.
kaushal_shah26
kaushal_shah26
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 30
Lowell (8/1/2012)
i think it's simply like this:

...ORDER BY ISNULL(SortOrder,2)



yes it's simple if I have only one record to update but unfortunately I have thousands records like this Sad
CapnHector
CapnHector
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 1789
i have a couple of things in mind but i think the OP is looking for an UPDATE statement. the question i have is that i cant see why condition ID 5 should have sort order 2. as condition id 3 has sort order 0 and condition id 2 has sort order 1. Also if condition ID 1 becomes active does the SortOrder have to change to reflect that or does it just get the next higher SortOrder.

how sortorder and the ID and condition ID relate will be the determining factor on which UPDATE statement is going to yield the correct results.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
kaushal_shah26
kaushal_shah26
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 30
capn.hector (8/1/2012)
i have a couple of things in mind but i think the OP is looking for an UPDATE statement. the question i have is that i cant see why condition ID 5 should have sort order 2. as condition id 3 has sort order 0 and condition id 2 has sort order 1. Also if condition ID 1 becomes active does the SortOrder have to change to reflect that or does it just get the next higher SortOrder.

how sortorder and the ID and condition ID relate will be the determining factor on which UPDATE statement is going to yield the correct results.


If inactive condition ever become active then we have other mechanisms to assign sortorder, so no worries for inactive conditions.

Thanks.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024
This should work with your sample data.


select *
from CustomerConditions
where IsActive = 1
order by SortOrder

begin transaction

update CustomerConditions
set SortOrder = x.RowNum
from CustomerConditions c
cross apply
(
select ConditionID, ROW_NUMBER() over (order by isnull(SortOrder, 9999999), ConditionID) - 1 as RowNum
from CustomerConditions
where IsActive = 1
) x
where x.ConditionID = c.ConditionID

select *
from CustomerConditions
where IsActive = 1
order by SortOrder

rollback transaction



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search