SQL Clone
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
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26020 Visits: 17528
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 Modens 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
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28127 Visits: 39939
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
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 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
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26020 Visits: 17528
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 Modens 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