update records based on condition

  • hi

    i have table , which can have millions of rows.

    Member primarycondition primaryintensity secondarycondition secondaryintensity

    M2345 hf 1

    M2345 COPD 1

    M2345 CAD 1

    M2345 dia 1

    M2345 Ast 1

    M2345 hf 2

    M2345 COPD 2

    M2345 CAD 2

    M2345 dia 2

    M2345 Ast 2

    what i need to do is if member has more than 1 condition, then

    i need to make 1 as a primary and another one secondary condition. with same primary intensity

    based on this order

    hf

    COPD

    CAD

    dia

    Ast

    so the result will be

    Member primarycondition primaryintensity secondarycondition secondaryIntensity

    M2345 hf 1

    M2345 COPD 1

    M2345 CAD 1

    M2345 dia 1

    M2345 Ast 1

    M2345 hf 2

    M2345 COPD 2

    M2345 CAD 2

    M2345 dia 2

    M2345 Ast 2

    i need to do it for all member,if member has more than 1 primary condition.

    any help?

  • I have tried to help you on so many threads and once again you haven't posted any details. If you would actually post ddl and sample data along with the desired output you will find that you receive answers a LOT faster and with a lot less posts. Please do yourself, and everyone on these forums a favor, if you are going to post asking for help then please make the effort to post ddl and sample data. Take a few minutes and read the first link in my signature.

    _______________________________________________________________

    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/

  • this is my DDL

    create table member

    (memberid int,

    primarycondition varchar(50),

    PrimaryIntensity int,

    secondarycondition varchar(50),

    Secondaryintensioty int)

    sample data i already provided in my previous thread

  • you DID NOT post readily consumable sample data.

    your sample headers had five columns, but the data only appears in three.

    how is anyone supposed to be able to use that?

    i ran what you posted thru my macro and came up with this:

    ;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])

    AS

    (

    SELECT 'M2345','hf','1' UNION ALL

    SELECT 'M2345','COPD','1' UNION ALL

    SELECT 'M2345','CAD','1' UNION ALL

    SELECT 'M2345','dia','1' UNION ALL

    SELECT 'M2345','Ast','1' UNION ALL

    SELECT 'M2345','hf','2' UNION ALL

    SELECT 'M2345','COPD','2' UNION ALL

    SELECT 'M2345','CAD','2' UNION ALL

    SELECT 'M2345','dia','2' UNION ALL

    SELECT 'M2345','Ast','2'

    )

    SELECT * FROM MySampleData

    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!

  • riya_dave (8/1/2013)


    this is my DDL

    create table member

    (memberid int,

    primarycondition varchar(50),

    PrimaryIntensity int,

    secondarycondition varchar(50),

    Secondaryintensioty int)

    sample data i already provided in my previous thread

    I am not a paid employee of this site. I do not want to go digging around through all your posts to find the information that you should be providing. I say this because I truly want to help, but you don't seem willing to put in even as much effort to a solution to your problems as you are expecting of other to do. I know that there a number of people around these forums who see certain posters names and simply skip on to the next one because they know the post is not going to have the details to offer much help without a lot of effort. Your name is certainly on that list. You need to make an effort at making it easy on the folks who are offering their advice free of charge.

    _______________________________________________________________

    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/

  • Lowell (8/1/2013)


    you DID NOT post readily consumable sample data.

    your sample headers had five columns, but the data only appears in three.

    how is anyone supposed to be able to use that?

    i ran what you posted thru my macro and came up with this:

    ;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])

    AS

    (

    SELECT 'M2345','hf','1' UNION ALL

    SELECT 'M2345','COPD','1' UNION ALL

    SELECT 'M2345','CAD','1' UNION ALL

    SELECT 'M2345','dia','1' UNION ALL

    SELECT 'M2345','Ast','1' UNION ALL

    SELECT 'M2345','hf','2' UNION ALL

    SELECT 'M2345','COPD','2' UNION ALL

    SELECT 'M2345','CAD','2' UNION ALL

    SELECT 'M2345','dia','2' UNION ALL

    SELECT 'M2345','Ast','2'

    )

    SELECT * FROM MySampleData

    You might also notice the "data" matches exactly what is stated as desired output. 😉

    _______________________________________________________________

    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/

  • my desire out put will be

    Member primarycondition primaryintensity secondarycondition secondaryIntensity

    M2345 hf 1

    M2345 COPD 1

    M2345 CAD 1

    M2345 dia 1

    M2345 Ast 1

    M2345 hf 2

    M2345 COPD 2

    M2345 CAD 2

    M2345 dia 2

    M2345 Ast 2

    it not taking spaces

    but hf and associated primaryintensity will be under primary condition and primaryintensity

    other will goes under secondarycondition and secondary intensity

  • riya_dave (8/1/2013)


    my desire out put will be

    Member primarycondition primaryintensity secondarycondition secondaryIntensity

    M2345 hf 1

    M2345 COPD 1

    M2345 CAD 1

    M2345 dia 1

    M2345 Ast 1

    M2345 hf 2

    M2345 COPD 2

    M2345 CAD 2

    M2345 dia 2

    M2345 Ast 2

    Since that matches exactly to what you said your data is like I guess you could select the rows from your table. Nothing changed between your table data and your output.

    _______________________________________________________________

    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/

  • Here you go, it generates the expected output based on your sample data.

    ;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])

    AS

    (

    SELECT 'M2345','hf','1','','' UNION ALL

    SELECT 'M2345','COPD','1','','' UNION ALL

    SELECT 'M2345','CAD','1','','' UNION ALL

    SELECT 'M2345','dia','1','','' UNION ALL

    SELECT 'M2345','Ast','1','','' UNION ALL

    SELECT 'M2345','hf','2','','' UNION ALL

    SELECT 'M2345','COPD','2','','' UNION ALL

    SELECT 'M2345','CAD','2','','' UNION ALL

    SELECT 'M2345','dia','2','','' UNION ALL

    SELECT 'M2345','Ast','2','',''

    )

    SELECT * FROM MySampleData

    By the way, I couldn't use your DDL because M2345 is not an int.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • thisis not the result that i want

    i have five PRIMARY condition ,

    hf,

    COPD,

    CAD,

    dia,

    ast

    primary intensity could be 1,2, 3

    now i want to check suppose if i have all 5 condition with same primary Intensity,

    HF and that intensity should be under primarycondition and primaryintensity

    other 4 should be move to secondarycondition and secondaryintensity

    and for that condition primarycondition and primaryintensity should update to null,

    now suppose i have just 3 primary condition , CAD,dia and ast with same intenstty

    then CAD and associated intensity should be primarycondition and primaryIntensity

    and dia and ast should be move to secondarycondition and secondaryIntensity

  • I think you will need to update the table against a version of itself featuring row_number().

    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!

  • but i need loop or condtion hwo to do it

    example if primarycondition = 'HF'

    then update table a

    set secondary condtion = null

    if there is no primary condition = 'HF'

    then

    if primarycondition = 'CAD'

    update table a

    set secondarycondition = primarycondition

    except for primarycondition ='CAD'

  • riya_dave (8/1/2013)


    but i need loop or condtion hwo to do it

    example if primarycondition = 'HF'

    then update table a

    set secondary condtion = null

    if there is no primary condition = 'HF'

    then

    if primarycondition = 'CAD'

    update table a

    set secondarycondition = primarycondition

    except for primarycondition ='CAD'

    Use ROW_NUMBER.

    If you need specific help with syntax you MUST post ddl and sample data in a consumable format. If you can do that you will get tested and accurate code back very quickly. If you continue to refuse to help us to help you, you will continue to get answers with no real details.

    You should read this article and think about how this relates to what you have posted in this thread.

    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

    _______________________________________________________________

    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/

  • i used row_number ,but how to get multiple condition in update

    for example if condition = 'HF'

    then update

    else if condition ='AST'

    then update

  • UPDATE SomeTable

    SET Column =

    CASE

    WHEN condition = 'HF'

    THEN SomeCalculationOrValueOrColumn

    WHEN condition2 = 'AST'

    THEN ADifferentCalculationOrValueOrColumn

    END

    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!

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

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