none
Update current row with previous row data.

    Question

  • Below is the dataset.

    UID, INVOICE

    1, 2100008

    2, +

    3, +

    4, +

    5, 2100009

    6, 2100010

    7,+

     

    I want to overwrite the plus signs from the invoice in the previous colums. So after I run the update process, then the new dataset would look like this

    New Dataset below.

    UID, INVOICE

    1, 2100008

    2, 2100008

    3, 2100008

    4, 2100008

    5, 2100009

    6, 2100010

    7, 2100010

     

    Any help would be appreciated.  Thanks

    Tuesday, May 17, 2011 8:59 PM

Answers

  • Declare @myTable Table (UID Int, Invoice Varchar(10))
    Insert Into @myTable 
    Select 1, '2100008' Union All 
    Select 2, '+' Union All 
    Select 3, '+' Union All 
    Select 4, '+' Union All 
    Select 5, '2100009' Union All 
    Select 6, '2100010' Union All 
    Select 7, '+' 
    
    ;With CTE 
    As
    (
    	Select 
    		* 
    		,ROW_NUMBER() Over (Order By UID) AS RN 
    	From 
    		@myTable 
    	Where 
    		1 = 1
    		And Invoice <> '+' 
    )
    
    	
    Update @myTable 
    Set Invoice = SubValue.Invoice
    From @myTable As MainQry
    	Outer Apply (Select * From CTE As SubQry Where MainQry.UID Between SubQry.UID And IsNull((Select Sub2Qry.UID From CTE As Sub2Qry Where Sub2Qry.RN = SubQry.RN + 1), MainQry.UID)) As SubValue
    Where 
    	MainQry.Invoice = '+'
    	
    	
    Select * From @myTable 
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Tuesday, May 17, 2011 9:55 PM
  • > Below are the create table and data insert.

    Thanks for the script, Unfortunately, the crappy forum software mangled it. There is a squiggly button in the posting interface. Use that to insert code samples.

    In the meanwhile... it seems that I managed completely to leave out your condition with the '+' character. What a lapse! Sorry about that. This should be better.

    UPDATE tbl
    SET    Invoice = (SELECT b.invoice
                      FROM   (SELECT invoice, rn = row_number() OVER(ORDER uid DESC)
                              FROM   tbl c
                              WHERE  c.uid < a.uid
                                AND  c.invoice <> '+') AS b
                      WHERE  b.rn = 1)
    FROM    tbl a
    WHERE   a.invoice = '+'

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 18, 2011 7:41 AM

All replies

  • Declare @myTable Table (UID Int, Invoice Varchar(10))
    Insert Into @myTable 
    Select 1, '2100008' Union All 
    Select 2, '+' Union All 
    Select 3, '+' Union All 
    Select 4, '+' Union All 
    Select 5, '2100009' Union All 
    Select 6, '2100010' Union All 
    Select 7, '+' 
    
    ;With CTE 
    As
    (
    	Select 
    		* 
    		,ROW_NUMBER() Over (Order By UID) AS RN 
    	From 
    		@myTable 
    	Where 
    		1 = 1
    		And Invoice <> '+' 
    )
    
    	
    Update @myTable 
    Set Invoice = SubValue.Invoice
    From @myTable As MainQry
    	Outer Apply (Select * From CTE As SubQry Where MainQry.UID Between SubQry.UID And IsNull((Select Sub2Qry.UID From CTE As Sub2Qry Where Sub2Qry.RN = SubQry.RN + 1), MainQry.UID)) As SubValue
    Where 
    	MainQry.Invoice = '+'
    	
    	
    Select * From @myTable 
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Tuesday, May 17, 2011 9:55 PM
  • UPDATE tbl
    SET    Invoice = (SELECT b.invoice
                      FROM   (SELECT invoice, rn = row_number() OVER(ORDER uid DESC)
                              FROM   tbl c
                              WHERE  c.uid < a.uid) AS b
                      WHERE  b.rn = 1)
    FROM    tbl a

    Note: this is an untested solution. Had you posted CREATE TABLE statements and INSERT statements with sample data, you would have gotten a tested solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 17, 2011 10:04 PM
  • Thanks Erland.

    Below are the create table and data insert.

    CREATE

    TABLE tbl

    (

    [UID] [int]

    IDENTITY(1,1) NOT NULL,
     

    [INVOICE] [nvarchar]

    (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    )

    ON [PRIMARY]

     

    INSERT

     

    INTO

    tbl

     

    (Invoice

    )

     

    VALUES

     

    ('2100008'

    )

    Go

    INSERT

     

    INTO

    tbl

     

    (Invoice

    )

     

    VALUES

     

    ('+'

    )

    Go

    INSERT

     

    INTO

    tbl

     

    (Invoice

    )

     

    VALUES

     

    ('+'

    )

    Go

    INSERT

     

    INTO

    tbl

     

    (Invoice

    )

     

    VALUES

     

    ('+'

    )

    Go

    INSERT

     

    INTO

    tbl

     

    (Invoice

    )

     

    VALUES

     

    ('2100009'

    )

    Go

    INSERT

     

    INTO

    tbl

     

    (Invoice

    )

     

    VALUES

     

    ('2100010'

    )

    Go

    INSERT

     

    INTO

    tbl

     

    (Invoice

    )

     

    VALUES

     

    ('+'

    )

    Go

    What I want to do is run this Update to find a record with the plus sign, then grab the previous row data and overwrite the plus sign.  I tried running the above process, but it really only moved the data into different rows and made the row the data was taken from then become null.
    Wednesday, May 18, 2011 3:54 AM
  • Hi,

    try this

    declare @t table(UD int, INVOICE varchar(15))
    insert into @t
    select 1, '2100008'
    union all
    select 2, '+'
    union all
    select 3, '+'
    union all
    select 4, '+'
    union all
    select 5, '2100009'
    union all
    select 6, '2100010'
    union all
    select 7,'+'

    select UD,case when INVOICE='+' then (select max(INVOICE) from @t t where t.UD<t1.UD) else INVOICE end

    from @t t1

     


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Wednesday, May 18, 2011 4:03 AM
  • > Below are the create table and data insert.

    Thanks for the script, Unfortunately, the crappy forum software mangled it. There is a squiggly button in the posting interface. Use that to insert code samples.

    In the meanwhile... it seems that I managed completely to leave out your condition with the '+' character. What a lapse! Sorry about that. This should be better.

    UPDATE tbl
    SET    Invoice = (SELECT b.invoice
                      FROM   (SELECT invoice, rn = row_number() OVER(ORDER uid DESC)
                              FROM   tbl c
                              WHERE  c.uid < a.uid
                                AND  c.invoice <> '+') AS b
                      WHERE  b.rn = 1)
    FROM    tbl a
    WHERE   a.invoice = '+'

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 18, 2011 7:41 AM
  • Use INSERT CODE BLOCK for posting code. Thanks.


    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Monday, May 23, 2011 10:16 PM
    Moderator