How to replace a cell value with a cell value in previous row

  • My table data is

    OrderNo SeqNo Type

    1234 1 BL

    1234 2 "

    1234 3 GL

    1235 1 PL

    1235 2 "

    For a order number, I want to replace Type = " as previous

    Type value of previous SeqNo.

    So, updated data should be

    OrderNo SeqNo Type

    1234 1 BL

    1234 2 BL

    1234 3 GL

    1235 1 PL

    1235 2 PL

    If anyone can highlight on this, I'll be very appreciated.

    Thanks in advance!!!

  • Your not giving us enough information.

    How do we know which row is the first row?

    The one with the value or the one that is blank.

    Do you have an identity column, or Date field to use to order.

    you have to remember data in a table has no order Unless there is a clustered index on a specific field.

    So First has no context with the data you have posted.

  • Actually... good post... I get it. To make future posts just absolutely perfect, take a look at the link in my signature below... THAT's how we like to see data posted and we'll normally respond to such correctly formatted posts very quickly.

    Here's your answer... and take a look at how I created the table and the data...that's what we'd like to see in future posts.

    Lemme know if it works for you...

    drop table #test

    go

    --===== Create a test table and populate it.

    -- This is the way we like to see data in posts to make it easier for us

    -- so you can get a better answer quicker.

    -- This is NOT a part of the solution.

    CREATE TABLE #Test

    (OrderNo INT, SeqNo INT, Type CHAR(2))

    INSERT INTO #Test

    (OrderNo, SeqNo, Type)

    SELECT 1234,1,'BL' UNION ALL

    SELECT 1234,2,'' UNION ALL

    SELECT 1234,3,'' UNION ALL

    SELECT 1234,4,'GL' UNION ALL

    SELECT 1235,1,'PL' UNION ALL

    SELECT 1235,2,''

    --===== Display the original content of the table

    SELECT OrderNo, SeqNo, Type

    FROM #Test t1

    ORDER BY OrderNo, SeqNo

    --===== Do a "data smear" using an ordered "Quirky" update.

    DECLARE @PrevOrderNo INT,

    @PrevType CHAR(2)

    UPDATE t1

    SET @PrevType = Type = CASE WHEN Type > '' THEN t1.Type ELSE @PrevType END,

    @PrevOrderNo = t1.OrderNo

    FROM #Test t1

    INNER JOIN

    (--==== This forces the order of the update

    SELECT TOP 100 PERCENT

    OrderNo, SeqNo

    FROM #Test

    ORDER BY OrderNo, SeqNo

    )t2

    ON t1.OrderNo = t2.OrderNo

    AND t1.SeqNo = t2.SeqNo

    --===== Display the new content of the table

    SELECT OrderNo, SeqNo, Type

    FROM #Test t1

    ORDER BY OrderNo, SeqNo

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

  • Ray M (1/23/2009)


    Your not giving us enough information.

    How do we know which row is the first row?

    The one with the value or the one that is blank.

    Do you have an identity column, or Date field to use to order.

    you have to remember data in a table has no order Unless there is a clustered index on a specific field.

    So First has no context with the data you have posted.

    Ray, the "order" is by OrderNo and SeqNo and a clustered index doesn't guarantee the order on SELECTs... it does on the "quirky" UPDATE, but only if it's in the order you want and only if you force an index scan on the UPDATE using a WITH(INDEX(0)) hint.

    If you don't mind code running a bit slower, you can force an ORDER BY on the UPDATE without a clustered index like I did in the code above.

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

  • Jeff tons of thanks for this solution. It works perfect for my data. I would definately like to know logic behind this if you can throw some light on this I will be highly appreciated.

  • He wrote an article on it actually. See the Running Totals link in my signature.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • hemin.shah85 (1/23/2009)


    Jeff tons of thanks for this solution. It works perfect for my data. I would definately like to know logic behind this if you can throw some light on this I will be highly appreciated.

    Seth is correct... I wrote an article on it and it's the one he pointed out.

    There is an over-statement in the article... you shouldn't use an index hint to sort a SELECT, always use an ORDER BY for those ... The method of using an index hint does work on the UPDATEs though.

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

  • Garadin (1/23/2009)


    He wrote an article on it actually. See the Running Totals link in my signature.

    Heh... I'm getting closer... normally, you beat me to this on providing solutions. I finally got one in but you still beat me at the explanation. 🙂

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

  • Hemin,

    There's another way to do the same thing that's actually much faster if you have a large table you need to do this on. It, too, is covered in the article that Seth pointed you to... I've changed the code above to use that faster method and I've updated the comments in the code to point out the changes... do read the comments... 😉 and do lookup the table hint of WITH(INDEX(0))... THAT's what makes it work on these types of UPDATES...

    drop table #test

    go

    --===== Create a test table and populate it.

    -- This is the way we like to see data in posts to make it easier for us

    -- so you can get a better answer quicker.

    -- This is now a part of the solution. Notice the NOT NULL's on the columns

    -- that we'll use in the clustered index (PK in this case)

    CREATE TABLE #Test

    (OrderNo INT NOT NULL, SeqNo INT NOT NULL, Type CHAR(2))

    INSERT INTO #Test

    (OrderNo, SeqNo, Type)

    SELECT 1234,1,'BL' UNION ALL

    SELECT 1234,2,'' UNION ALL

    SELECT 1234,3,'' UNION ALL

    SELECT 1234,4,'GL' UNION ALL

    SELECT 1235,1,'PL' UNION ALL

    SELECT 1235,2,''

    --===== This is part of the change... it's a Clustered Index (PK in this case)

    -- that the quirky update will use to SCAN in the correct order

    -- without an ORDER BY. Again, DO NOT USE for SELECTs. It only

    -- works for the "quirky update" method. There, it works whether

    -- the index/constraint is named, or not. If it's a PK constraint,

    -- it shouldn't be named on TempTables because constraint names

    -- must be unique whereas index names do not need to be.

    ALTER TABLE #Test

    ADD PRIMARY KEY CLUSTERED (OrderNo, SeqNo)

    --===== Display the original content of the table

    SELECT OrderNo, SeqNo, Type

    FROM #Test t1

    ORDER BY OrderNo, SeqNo

    --===== Do a "data smear" using an ordered "Quirky" update.

    DECLARE @PrevOrderNo INT,

    @PrevType CHAR(2)

    -- This is the other part of the change. The quirky update will always do

    -- things in the correct order (assumming the clustered index is on the

    -- correct columns) IF you can force a clustered index scan. The WITH(INDEX(0))

    -- directive (table hint) forces that scan. As you can see, it'll do two things...

    -- it makes the code a heck of a lot shorter and it makes it lightning fast.

    -- This method will update a million rows in less than 7 seconds.

    -- Basically, it reads a row, set's all the variables as if you were using VB or C,

    -- and writes the change all in one statement. NASTY FAST!

    UPDATE t1

    SET @PrevType = Type = CASE WHEN Type > '' THEN t1.Type ELSE @PrevType END,

    @PrevOrderNo = t1.OrderNo

    FROM #Test t1 WITH(INDEX(0))

    --===== Display the new content of the table

    SELECT OrderNo, SeqNo, Type

    FROM #Test t1

    ORDER BY OrderNo, SeqNo

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

  • Jeff Moden (1/24/2009)


    Garadin (1/23/2009)


    He wrote an article on it actually. See the Running Totals link in my signature.

    Heh... I'm getting closer... normally, you beat me to this on providing solutions. I finally got one in but you still beat me at the explanation. 🙂

    Way to hold out on me with the whole Order by trick by the way!

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Jeff you are right. With(index(0)) works really fast...as I have a dataset of 6 million records..Thanks for your help..But I was thinking, can we do this without updating that is just with SELECT statement so as to display the values only..

  • hemin.shah85 (1/24/2009)


    Jeff you are right. With(index(0)) works really fast...as I have a dataset of 6 million records..Thanks for your help..But I was thinking, can we do this without updating that is just with SELECT statement so as to display the values only..

    Unfortunately, no, not with this approach.

    You see, SELECT has a tiny restriction with respect to variable assignments: a SELECT statement cannot assing to both variables and to column outputs. Since variables are scalar and singular they cannot hold more than one row "cell"'s worth of data. On the other hand, output column values are distinct by row and cannot be referenced outside of their row. The UPDATE trick works by using both together: output columns to output each distinct row's values, and variables to carry values across rows: from one row to the next.

    So this one tiny restriction prevents the whole trick from working with SELECT (unless you only want a single row's worth of output).

    ...

    Now having said that, let me also say that several months ago, I figured out a way to do the UPDATE trick with a couple of SELECT's. Unfortunately it is both complex and slow. In fact, it is typically slower than several other ways of doing the same thing, including cursors and loops most of the time. (I have been toying around with a couple of possibilities for making it faster, but it makes this complex technique even more so).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • hemin.shah85 (1/24/2009)


    Jeff you are right. With(index(0)) works really fast...as I have a dataset of 6 million records..Thanks for your help..But I was thinking, can we do this without updating that is just with SELECT statement so as to display the values only..

    I'm thinking that you didn't actually read the article you were pointed to and suggest that you actually take the time to do that. 😉 There, you'll find suggestions on what to do when you can't actually update the source table. Some of those options include the use of SELECT/INTO (a very high speed method) of the data you need to do the mod on into a TempTable, do the update on THAT, and then display from there. It'll still be light years ahead of any other method you can use for this.

    Like I said, read the article...

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

  • Garadin (1/24/2009)


    Way to hold out on me with the whole Order by trick by the way!

    BWAA-HAA!! 😛 Dude! 😀 No hold out... it's in the article, too! :hehe:

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

  • Jeff Moden (1/24/2009)


    hemin.shah85 (1/24/2009)


    Jeff you are right. With(index(0)) works really fast...as I have a dataset of 6 million records..Thanks for your help..But I was thinking, can we do this without updating that is just with SELECT statement so as to display the values only..

    I'm thinking that you didn't actually read the article you were pointed to and suggest that you actually take the time to do that. 😉 There, you'll find suggestions on what to do when you can't actually update the source table. Some of those options include the use of SELECT/INTO (a very high speed method) of the data you need to do the mod on into a TempTable, do the update on THAT, and then display from there. It'll still be light years ahead of any other method you can use for this.

    Just to clarify my earlier response to Hemin's question: I was assuming that he was asking about using SELECT pseudocursors to do this. There are of course lots of ways to accomplish the same result, many of which use SELECT at one point or another.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

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

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