Update sortorder column to +1

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

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

  • 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 (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.

  • 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 πŸ™

  • 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[/url] 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[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

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

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • kaushal_shah26 (8/1/2012)


    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.

    ok so that answers one question. the other question is how sort order relates to any thing else. is there any reason to the sort order ie. Is there a reason conditionID 3 has a sort order of 0 and conditionID 2 has sortorder of 1?


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] 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[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Sean Lange (8/1/2012)


    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

    Thanks for the code. It works for the sample code. Now, How do we do this for all types. In the sample code, we only have one type which is 860. In my table I have hundreds of types which have similar data error.

    For those, who are wondering why this happened in the database, it's because of some DBA diddn't do his job properly six months ago and now, we are finding out that some of the data are not showing up properly on the screen and by tracing back to data, we figured out that those conditions are active but they don't have sortOrder.

    Anyway, I really appreciate your help and if you could help me to solve for all types, I would never forgot you πŸ™‚

  • You're better off using a CTE than the CROSS APPLY.

    WITH ActiveUpdates AS (

    SELECT [ID], SortOrder, isActive, ROW_NUMBER() OVER( ORDER BY SortOrder DESC, [ID] ) - 1 AS rn

    FROM @CustomerConditions

    )

    UPDATE ActiveUpdates

    SET SortOrder = rn

    WHERE isActive = 1

    AND SortOrder IS NULL;

    Here are the stats.

    CTE

    Table '#64FCD10B'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    CROSS APPLY

    Table '#65F0F544'. Scan count 7, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    As you can see the CTE only scans the table once, but the CROSS APPLY scans the table once for the outer query and once for every single row in the outer query to get the results for the subquery. With such a small table, you won't notice much of a difference, but, as you add rows to the table, the difference will become marked.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • kaushal_shah26 (8/2/2012)


    Thanks for the code. It works for the sample code. Now, How do we do this for all types. In the sample code, we only have one type which is 860. In my table I have hundreds of types which have similar data error.

    You will just need to add a PARTITION clause to the OVER() function to partition on type.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (8/2/2012)


    You're better off using a CTE than the CROSS APPLY.

    WITH ActiveUpdates AS (

    SELECT [ID], SortOrder, isActive, ROW_NUMBER() OVER( ORDER BY SortOrder DESC, [ID] ) - 1 AS rn

    FROM @CustomerConditions

    )

    UPDATE ActiveUpdates

    SET SortOrder = rn

    WHERE isActive = 1

    AND SortOrder IS NULL;

    Here are the stats.

    CTE

    Table '#64FCD10B'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    CROSS APPLY

    Table '#65F0F544'. Scan count 7, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    As you can see the CTE only scans the table once, but the CROSS APPLY scans the table once for the outer query and once for every single row in the outer query to get the results for the subquery. With such a small table, you won't notice much of a difference, but, as you add rows to the table, the difference will become marked.

    Drew

    Good call Drew. I tend to forget using a CTE for updates. Will have to remember to keep that in the arsenal.

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The sample data doesn't contain any holes. Maybe there aren't any. Here's a sample set with a hole and two [Type]'s:

    SET NOCOUNT ON

    DROP TABLE #CustomerConditions

    CREATE TABLE #CustomerConditions(

    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [Type] [int] NULL,

    [ConditionId] [int] NULL,

    [isActive] [bit] NULL,

    [SortOrder] [int] NULL

    );

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

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

    insert into #CustomerConditions (Type,ConditionId,isActive,SortOrder) values (850,5,1,3);

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

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

    insert into #CustomerConditions (Type,ConditionId,isActive,SortOrder) values (850,4,0,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);

    CREATE INDEX ix_Type ON #CustomerConditions ([Type], SortOrder)

    SELECT *

    FROM #CustomerConditions

    ORDER BY [Type], ID

    Here's a solution which works if there are holes;

    ;WITH MyTable AS (

    SELECT

    rn = ROW_NUMBER() OVER(PARTITION BY c.[Type] ORDER BY c.ConditionId),

    t.MAX_SortOrder,

    c.*

    FROM #CustomerConditions c

    INNER JOIN (

    SELECT [Type], MAX_SortOrder = MAX(SortOrder)

    FROM #CustomerConditions

    GROUP BY [Type]

    ) t ON t.[Type] = c.[Type]

    WHERE c.IsActive = 1 AND c.SortOrder IS NULL

    ) UPDATE MyTable SET SortOrder = rn + MAX_SortOrder

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply