Update with cursors

  • Hi,

    I have a table which has itemno,itemID,newprice,oldprice,changedate,seqid

    the data is like this :-

    a123 12345 5.50 2.30 2012-03-06 1

    a123 12345 5.25 2.65 2012-01-09 2

    a123 12345 5.30 2.88 2011-11-13 3

    a127 67890 2.1 1.4 2012-03-05 1

    a127 67890 2.4 1.7 2012-01-29 2

    a127 67890 2.18 1.8 2011-09-12 3

    There are 7-8 itemno with 3 rows of data each.

    I want to update the latest itemno with new price and old price.

    For ex

    desired output :--

    a123 12345 5.50 5.25 2012-03-06 1

    a127 67890 2.1 2.4 2012-03-05 1

    If you note, I just want updating the (seqid =1 for each itemnmbr)

    with the oldprice =latest newprice(seqid=2),

    BUT only one thing I want to check that if oldprice =latest newprice, then it should take the value from seqid =3( that is more old (newprice)

    So it would be like a cursor, it would go until old price <> latest newprice.

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • You know with over 1,100 points you should know how to post a question. ddl, sample data, desired output.

    Take a look at 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/

  • No, no cursor necessary, but as Sean pointed out we need the DDL for the table, sample data for the table, expected results when all is done. It would also be nice to see what you have done so far to solve your problem.

    Be sure to read the article Sean suggested. You will also find a link to it in my signature block as well.

  • I dont think DDL is needed and sample data i already put..

    Leave the question if u can't help.

    Regards
    Sushant Kumar
    MCTS,MCP

  • SKYBVI (3/7/2012)


    I dont think DDL is needed and sample data i already put..

    Leave the question if u can't help.

    It's not that we can't help. It's that you can't help us to help you. This does not need to be done with a cursor but I am not going to write up some pseudocode which would require me to write ddl for your problem to make sure that I was posted was not incorrect.

    The point here is to make it easy for people to help you. That means give them something to work with. You are expecting people to help you with code but we don't know datatypes, table names etc...and unless you are paying me I am NOT going to waste my time helping you put together the setup so I can work on your issue.

    How do you expect somebody to come up with anything resembling decent code when you didn't give enough details in the first place?

    _______________________________________________________________

    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/

  • SKYBVI (3/7/2012)


    I dont think DDL is needed and sample data i already put..

    Leave the question if u can't help.

    Everyone that has posted in this thread could help. But you seem unwilling to accept it.

    If I paste the "sample data" you have posted into management studio, what happens?

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax

    Instead, post your sample data in a readily consumable format, e.g.

    CREATE TABLE yourTable (itemno CHAR(4),itemID INT, newprice MONEY, oldprice MONEY, changedate DATETIME, seqid TINYINT)

    INSERT INTO yourTable

    SELECT 'a123', 12345, 5.50, 2.30, '2012-03-06', 1

    UNION ALL SELECT 'a123', 12345, 5.25, 2.65, '2012-01-09', 2


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • SKYBVI (3/7/2012)


    I dont think DDL is needed and sample data i already put..

    Leave the question if u can't help.

    This response is unprofessional. If you want our help, you need to help us. There are people out here willing to help you but we aren't going to take the time to figure out you table definitions, write up dml to populate those tables and then work on solving your problem when 1) we are volunteers offer to help on our own time and 2) there are others out there willing to provide us with the information we need to help them.

  • SKYBVI (3/6/2012)


    Hi,

    I have a table which has itemno,itemID,newprice,oldprice,changedate,seqid

    the data is like this :-

    a123 12345 5.50 2.30 2012-03-06 1

    a123 12345 5.25 2.65 2012-01-09 2

    a123 12345 5.30 2.88 2011-11-13 3

    a127 67890 2.1 1.4 2012-03-05 1

    a127 67890 2.4 1.7 2012-01-29 2

    a127 67890 2.18 1.8 2011-09-12 3

    There are 7-8 itemno with 3 rows of data each.

    I want to update the latest itemno with new price and old price.

    For ex

    desired output :--

    a123 12345 5.50 5.25 2012-03-06 1

    a127 67890 2.1 2.4 2012-03-05 1

    If you note, I just want updating the (seqid =1 for each itemnmbr)

    with the oldprice =latest newprice(seqid=2),

    BUT only one thing I want to check that if oldprice =latest newprice, then it should take the value from seqid =3( that is more old (newprice)

    So it would be like a cursor, it would go until old price <> latest newprice.

    Regards,

    Skybvi

    No problem. Simply build a CTE with ROW_NUMBER() OVER and a MIN comparison for dupes sorted in descending order with an outer update of row number 1 with row number 2 and you're done.

    --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 8 posts - 1 through 7 (of 7 total)

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