Update sortorder column to +1

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » Update sortorder column to +1

Add to briefcase

19 posts, Page 1 of 2

1 2 »»

Update sortorder column to +1

Expand / Collapse

Author

Message

Posted Wednesday, August 01, 2012 2:25 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 10:35 AM
Points: 20, Visits: 24

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.

Post #1338807

Posted Wednesday, August 01, 2012 2:29 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 8,620, Visits: 8,261

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

Post #1338809

Posted Wednesday, August 01, 2012 2:40 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 10:35 AM
Points: 20, Visits: 24

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.

Post #1338817

Posted Wednesday, August 01, 2012 2:47 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,645, Visits: 27,734

i think it's simply like this:

...ORDER BY ISNULL(SortOrder,2)

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1338823

Posted Wednesday, August 01, 2012 3:04 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 10:35 AM
Points: 20, Visits: 24

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.

Post #1338838

Posted Wednesday, August 01, 2012 3:05 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 10:35 AM
Points: 20, Visits: 24

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

Sad

Post #1338840

Posted Wednesday, August 01, 2012 3:06 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

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

Jeremy Oursler

Post #1338841

Posted Wednesday, August 01, 2012 3:19 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Here is my guess at what your picture meant. We do not even know the name of the table

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
something_type SMALLINT NOT NULL,
something_condition SMALLINT NOT NULL,
non_relational_status_flag BIT NOT NULL,
non_relational_ordering_flag BIT);

INSERT INTO Foobar
VALUES
(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. <<

'active' is a status value, a state of being. That means it has a duration, a temporal dimension. Where is that in the DDL? This is one of the many ways that RDBMS is not like punch card processing.

>> Out of those 3 conditions, 2 have non_relational_ordering_flag value (1 and 0). <<

The sort order is meta data and should never be in the table. And what does NULL mean in the context of a sorting order?

>> I need to write a script where I need to update the record [sic: rows are not records; punch cards are also called unit records, BTW] where condition is active [unh? The something condition is a numeric value and not a flag in your narrative`] but non_relational_ordering_flag IS NULL (In the case above, it's something_condition = 5).

>> I need to update the non_relational_ordering_flag with the next value of the maximum non_relational_ordering_flag for the type where on_relational_status_flag is 1. <<

That update would be how we set flags when we worked with punch cards. Today, we would use a predicate to test for the current state of the data.

Please start over and give us enough information to get you a workable, properly normalized schema. But you are going to have to learn a lot of new concepts and get rid of the old ones. A SQL is not the best place to get an education.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1338846

Posted Wednesday, August 01, 2012 3:22 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 10:35 AM
Points: 20, Visits: 24

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.

Post #1338849

Posted Wednesday, August 01, 2012 3:25 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 8,620, Visits: 8,261

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

Post #1338852

« Prev Topic | Next Topic »

Add to briefcase

19 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse