How to update different column depends on seq

  • I'm realtive new to SQL coding, I have a question on my below code. I'm trying to update differnt column on same table based on the @incr, this variable is associated with a client, which could range from 1 to 10 based on differnt client. I'm currently use cursor to do this one by one. is there anyway i can avoid using cursor in this situation ? Code is something like

    declare My_Cur for select GNo, Gvd, Gva from TableA order by date

    open My_Cur

    fetch next from My_cur into @GNo, @GVD, @Gva

    while @@FETCH_STATUS = 0

    Begin

    If @Incr = 1 Begin update TableA set ADate001 = @GVD, AAmt001 = @Gva where GNo = @G_No End

    If @Incr = 2 Begin update TableA set ADate002 = @GVD, AAmt002 = @Gva where GNo = @G_No End

    If @Incr = 3 Begin update TableA set ADate003 = @GVD, AAmt003 = @Gva where GNo = @G_No End

    If @Incr = 4 Begin update TableA set ADate004 = @GVD, AAmt004 = @Gva where GNo = @G_No End

    ...

    set @VestIncr = @VestIncr + 1

    fetch next from my_cursorV into @GrantNumi, @GVD, @Gva

    end

  • Yes, there like is. As of right though - you're not specifying where or how you're getting @incr (you use it, but you're not retrieving /set its value.). Are you pulling it out of the cursor, or is that globally set ahead of time? It just sounds like you're not describing enough to give you a complete answer: try to supply some more specifics on how that would be derived.

    Depending on how that's set, there would be several options on how to approach that.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • This screams poor table design... why don't you have one row per date?

    If this is the case of a report you need to run, or something of the likes, or that you can't change the table design then answer this question :

    how do you set this value : @Incr?

    Is it simply random or is there something in the base table that helps us identify this value?

  • Guess why, it is a vendor software, I can't change table design.

    The vendor send a large ASCII file for multiple clients which it loaded to a staging table.

    The @Incr initilzed at 1 in the beginning of cursor, if you noticed the original SQL has an order by which identify the value. But as I mentioend, I can't tell how many increment each client has until read from input table.

  • I really appreciate someone come up help so fast. OK before there is another piece of code to open another cursor like below (its like cursor in a cursor which I know is performance killer, staging tbale has almost one million and need popluate data to mutilple million rows tableA)

    declare MG cursor for

    select Grant_number_G from staging_tbl

    open my_cursorG

    set @VestIncr = 1

    fetch next from MG into @GNo

    while @@FETCH_STATUS = 0

    BEGIN

    ......

  • John - The little abbreviated snippets just don't make sense to me. i just can't quite figure out how they're going together.

    Let's start basic - try to start by laying out some basic info to show what's there and where things need to go. Pulling one row out of the staging table at a time just plain doesn't make sense if you can do it set-based, but with the info we have now - I couldn't tell you what to implement. seeing what the incoming data, the staging table, and the final tables look like would be important too.

    Anyway - start here:

    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

    Try to give us as much info on the problem as you can. Help us help you.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Let me try it I can do better this time, below is the code and some comments

    -- read client data from staging table and basd on the increment value, update TableA corresponding columns accordingly

    declare MG cursor for

    select C_Number from staging_tbl

    open MG cursor

    set @VestIncr = 1

    fetch next from MG into @GNum

    while @@FETCH_STATUS = 0

    BEGIN

    declare My_Cur for select GNo, Gvd, Gva from TableA order by Gdate -- Gdate is a date column,

    open My_Cur

    fetch next from My_cur into @GNo, @GVD, @Gva

    while @@FETCH_STATUS = 0

    Begin

    If @Incr = 1 Begin update TableA set ADate001 = @GVD, AAmt001 = @Gva where GNo = @G_No End

    If @Incr = 2 Begin update TableA set ADate002 = @GVD, AAmt002 = @Gva where GNo = @G_No End

    If @Incr = 3 Begin update TableA set ADate003 = @GVD, AAmt003 = @Gva where GNo = @G_No End

    If @Incr = 4 Begin update TableA set ADate004 = @GVD, AAmt004 = @Gva where GNo = @G_No End

    ... -- somehow it can have up to 10 increment

    end

    set @VestIncr = @VestIncr + 1

    fetch next from my_cursorV into @GrantNumi, @GVD, @Gva

    end

    ....

  • John Chiu (6/18/2008)


    Let me try it I can do better this time, below is the code and some comments

    -- read client data from staging table and basd on the increment value, update TableA corresponding columns accordingly

    declare MG cursor for

    select C_Number from staging_tbl

    open MG cursor

    set @VestIncr = 1

    fetch next from MG into @GNum

    while @@FETCH_STATUS = 0

    BEGIN

    declare My_Cur for select GNo, Gvd, Gva from TableA order by Gdate -- Gdate is a date column,

    open My_Cur

    fetch next from My_cur into @GNo, @GVD, @Gva

    while @@FETCH_STATUS = 0

    Begin

    If @Incr = 1 Begin update TableA set ADate001 = @GVD, AAmt001 = @Gva where GNo = @G_No End

    If @Incr = 2 Begin update TableA set ADate002 = @GVD, AAmt002 = @Gva where GNo = @G_No End

    If @Incr = 3 Begin update TableA set ADate003 = @GVD, AAmt003 = @Gva where GNo = @G_No End

    If @Incr = 4 Begin update TableA set ADate004 = @GVD, AAmt004 = @Gva where GNo = @G_No End

    ... -- somehow it can have up to 10 increment

    end

    set @VestIncr = @VestIncr + 1

    fetch next from my_cursorV into @GrantNumi, @GVD, @Gva

    end

    ....

    John - this still does not help us. Where are you setting @Incr? I don't see that at all anywhere in your script - but I do see that you set @VestIncr and increment that variable. It is not, however, used anywhere that I can see.

    Please, please, please follow the instructions previously referenced. Those instructions show you how to setup/build the create table statements, insert statements to populate the tables and outlines how to identify the expected results. Without that information it is going to be very hard for anybody to provide an answer to this question.

    Thanks,

    Jeff

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Below is actual code (except decalre variables). Hope you can shed some light to see how I may can make it works better

    declare MG cursor for

    select C_Number from staging_tbl

    open MG cursor

    set @VestIncr = 1

    fetch next from MG into @GNum

    while @@FETCH_STATUS = 0

    BEGIN

    declare My_Cur for select GNo, Gvd, Gva from TableA order by Gdate -- Gdate is a date column,

    open My_Cur

    fetch next from My_cur into @GNo, @GVD, @Gva

    while @@FETCH_STATUS = 0

    Begin

    If @VestIncr = 1 Begin update TableA set ADate001 = @GVD, AAmt001 = @Gva where GNo = @G_No End

    If @VestIncr = 2 Begin update TableA set ADate002 = @GVD, AAmt002 = @Gva where GNo = @G_No End

    If @VestIncr = 3 Begin update TableA set ADate003 = @GVD, AAmt003 = @Gva where GNo = @G_No End

    If @VestIncr = 4 Begin update TableA set ADate004 = @GVD, AAmt004 = @Gva where GNo = @G_No End

    ... -- somehow it can have up to 10 increment

    set @VestIncr = @VestIncr + 1

    fetch next from My_cur into @GNo, @GVD, @Gva

    end

    set @VestIncr = 1

    close My_Cur

    deallocate My_Cur

    fetch next from MG into @G_Num

    end

    close MG

    deallocate MG

  • John - start here: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    I have no idea how to help you out with this, you have posted a new script with changes to it and it just opens up more questions. For example, why are you even populating the variable @Gnum? It is not used, as far as I can tell, and that cursor only appears to be used to increment a variable.

    Please read the above link and put together the requested information. I am absolutely positive that if you provide the above information someone here will be able to provide a solution.

    Jeff

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Somebody needs to find whoever designed this process on the vendor side, and beat them with a data normalization book. They should go back to designing spreadsheets because they clearly don't have a clue about how to lay out data. It also seems that they want you to rely on the physical order in the staging_tbl, which is another clue that they don't know much about RDBMS'es.

    I honestly don't think you'll get much perf gains out of this, but you never know. The design is so bad that there really doesn't seem to be much in the way of improvements left.

    You can try something like this:

    select identity(int,1,1) as rid,

    c_number

    into #myTable

    from staging_tbl

    update TableA

    set ADate001 = Gvd,

    AAmt001 = Gva

    from tableA

    inner join #myTable on GNo=c_number

    where #myTable.rid=1

    update TableA

    set ADate002 = Gvd,

    AAmt002 = Gva

    from tableA

    inner join #myTable on GNo=c_number

    where #myTable.rid=2

    update TableA

    set ADate003 = Gvd,

    AAmt003 = Gva

    from tableA

    inner join #myTable on GNo=c_number

    where #myTable.rid=3

    --keep going until 10

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Suppressing... urge... to... load... porkchops... on... OP...

    PLEASE! You haven't read the article that everyone has been asking you to... let's try this one final time... read, understand, and post using the techniques found in the following article, please.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

    Change is inevitable... Change for the better is not.


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

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

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