Updating Column Where Value is Null with Previous Value

  • Jersey Moe

    SSChasing Mays

    Points: 652

    Looking for some suggestions on how to perform an update. Here is the task.

    One table has two columns (Group_Name and User_ID). the User_ID is always populated but, the Group_Name is not. Data extract parametes can not be adjusted and no way to get at back end db either.

    A user_id can appear in more then one group. but the group name is unique.. so its a many to one condition. Group_name would be the value to relate to other tables.

    table data looks like this after the initial data load:

    Group1.User1

    NULL....User2

    NULL....USER2

    Group2.User1

    NULL....User5

    Group3.User6

    NULL....User9

    ...

    How to I update the table to look like this so that I can run a group by:

    Group1.User1

    Group1.User2

    Group1.USER2

    Group2.User1

    Group2.User5

    Group3.User6

    Group3.User9

    ...

    Appreciate any thoughts or ideas.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88112

    First, you need to identify how a user is associated to a particular group. Looking at the data, there is no way to tell that User2 belongs to Group1. How do you determine what group the user belongs to?

    If you say it is because of the order the rows are returned - then you need to provide the columns that determine that order. We cannot rely upon insertion order either, unless you have some column available that defines when that row was inserted.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Leju Geevarghese

    SSC Eights!

    Points: 982

    Am I am guessing your question correctly, when ever a Null is encountered in the group field, you want to update the null to previous records group value which is not Null?

    ---------------------------------------------------------------------------------------
    It begins by taking the first step.

  • parthi-1705

    SSCrazy Eights

    Points: 9277

    Jeff is correct unless you dont have a unique value you will not be able update the prv column with your data columns.you dont have any unique field as id or datetime in your table so you cannt be able to update those cloumns. On what condition you will update how do u know when the data has been inserted in which order.Unless and until u dont have a unique column you will be able to update in this case. Study normalization,Codds rule this discribes more on this.how to create a table on which bases.Hope you could have got some idea what to do now

    Thanks

    Parthi

    Thanks
    Parthi

  • Jersey Moe

    SSChasing Mays

    Points: 652

    Yes, I would like to update the 'Null' value to the previous non 'Null' value

  • Jersey Moe

    SSChasing Mays

    Points: 652

    so if I understan, you are suggesting adding an identiklty column. This way I can address a row via a unique value. Yes?

  • Leju Geevarghese

    SSC Eights!

    Points: 982

    Now Parthi's previous comments comes into play, may be you have another field in the table that identifies how the rows were inserted. List the entire table definition

    ---------------------------------------------------------------------------------------
    It begins by taking the first step.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    You add an identity column to hold ordering in the same positions they're already stuck in. Then you modify a few things like maxdop to make sure you don't shoot your ordering in the foot.

    Then you would do a 'running totals' trick to get the last value to the next row. See this article for some details:

    http://www.sqlservercentral.com/articles/T-SQL/68467/

    What it basically does is carry the previous entry to the next line, allowing you to transfer it over. So you'll end up with a statement (this is psuedocode) like this:

    DECLARE @carryme VARCHAR(1000)

    UPDATE tbl

    SET

    @carryme = NullableCol = @carryme

    FROM

    tbl

    ORDER BY

    tblID


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Leju Geevarghese

    SSC Eights!

    Points: 982

    You could do that and verify if the rows are listed in order you had inserted.

    Then open a cursor, loop through the records ordered by this identity field and update the group field records.

    ---------------------------------------------------------------------------------------
    It begins by taking the first step.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Sorry, bad code, it should have been:

    UPDATE tbl

    SET

    @carryme = NullableCol = ISNULL( NullableCol, @carryme)

    FROM

    tbl

    ORDER BY

    tblID

    Oops.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Jersey Moe

    SSChasing Mays

    Points: 652

    cool trick, I'll give it try. Thanks for the replies>

  • Jeff Moden

    SSC Guru

    Points: 994648

    Jersey Moe (10/6/2010)


    cool trick, I'll give it try. Thanks for the replies>

    It may be a cool trick but you MUST not use it in this case. Unless you simply didn't post it, you have NOTHING in the data that will guarantee the order of the rows which is absolutely essential to doing the job correctly. Somehow you have to load the data with at least an IDENTITY or other incrementing column to guarantee the order. That's true even for a cursor never mind the Quirky Update.

    I say again... unless you have something in the table that is both unique and can identify the correct order of the rows, you're pretty much stuck. Don't give the "Quirky Update" a bad name by using it inappropiately. Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 12 (of 12 total)

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