Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Unanswered: "expanding" updates

    Hi,
    i need to "expand" a raw datatable like

    1, 'Harry', 'London'
    2, 'Peter', null
    3, null, 'New York'

    into this format

    1, 'Harry', 'London'
    2, 'Peter', 'London'
    3, 'Peter', 'New York'

    so, for several columns, null values should be replaced with the values of the previous (non-null) row. the raw data is bulk-inserted and cannot be modified before.

    is there anything other (faster!) than using a cursor ?
    i like to have on single update command to do the job.
    Last edited by msieben; 06-02-03 at 12:41.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    does each row in the table have a unique sequential key?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Originally posted by Paul Young
    does each row in the table have a unique sequential key?
    yes, the first column is an identity

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    why not join the table to its self based on the identity column?

    Code:
    select *
      from yourbcptable ybt1
      join yourbcptable ybt2 on ybt1.IdentityColumn - 1 = ybt2.IdentityColumn
     where ybt1.IdentityColumn > 1
    This assumes your identity seed = 1
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    yes, the identity seed is 1, so your select works,

    but what about multiple null rows in order like

    1, 'Peter', 'london'
    2, 'Markus', null
    3, 'Mary', null

    for row 3 the row (id-1) will be null as well?

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    either a row at a time approach or run the update till you no longer have nulls or wrap everything up in a stored procedure.


    probably should have added more to the where caluse

    where ybt1.IdentityColumn > 1
    and ytb1.col1 is null or ybt1.col2 is null


    BTW, I would NOT use a CURSOR as these tend to be resource intensive.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    i got closer. the following update will nearly do the job, but the update order needs to be by the identity column. is there a way to set the order for updates ?


    update mytable set
    @Column1 = Column1 = isnull( Column1, @Column1 ),
    @Column2 = Column2 = isnull( Column2, @Column2 )


    this will set column1 to column1 (keep the value) when it is not null or will set column1=@column1, which holds then "not null"-value of the rows before. works fine - but only when processed in correct order !

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    No guaranties on the order. SQL Server will process in the most efficient manner.

    You might try setting a clustered index on the key, but I would check the results very closely.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    --create table T (a int identity, N varchar(10), C varchar(10))
    /*
    delete from T
    insert into T (n,c) select 'Harry','London'
    insert into T (n,c) select 'Peter',null
    insert into T (n,c) select null,'New York'
    */
    select * from T
    update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
    from T nxt join T prv on nxt.a=prv.a+1
    select * from T
    --
    kukuk

  10. #10
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Originally posted by kukuk
    --create table T (a int identity, N varchar(10), C varchar(10))
    /*
    delete from T
    insert into T (n,c) select 'Harry','London'
    insert into T (n,c) select 'Peter',null
    insert into T (n,c) select null,'New York'
    */
    select * from T
    update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
    from T nxt join T prv on nxt.a=prv.a+1
    select * from T
    correct - that's what we discussed before,
    but what about multiple null rows in order like

    1, 'Peter', 'london'
    2, 'Markus', null
    3, 'Mary', null

    for row 3 the row (id-1) will be null as well?
    there might be many null-rows after one data-row.

  11. #11
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81


    while exists (select * from T where a>1 and (C is null or N is null) )
    update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
    from T nxt join T prv on nxt.a=prv.a+1

    (will work only if the first row does not contain nulls)

    or

    update x set
    N=isnull(N,(select top 1 N from T as y where x.a>y.a and y.N is not null order by a desc)),
    C=isnull(C,(select top 1 C from T as z where x.a>z.a and z.C is not null order by a desc))
    from T as x

    (expensive one)
    --
    kukuk

  12. #12
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Originally posted by kukuk


    while exists (select * from T where a>1 and (C is null or N is null) )
    update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
    from T nxt join T prv on nxt.a=prv.a+1

    (will work only if the first row does not contain nulls)

    or

    update x set
    N=isnull(N,(select top 1 N from T as y where x.a>y.a and y.N is not null order by a desc)),
    C=isnull(C,(select top 1 C from T as z where x.a>z.a and z.C is not null order by a desc))
    from T as x

    (expensive one)
    the first one seems to be the one ...
    i'll give it a try and come back later

  13. #13
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    hi kukuk,

    i think i got it:

    Code:
    declare @A int, @B varchar(20), @C varchar(20)
    
    select top 1 @A = A from Tbl order by ID 
    while @@RowCount>0 begin
      update Tbl
        set @B= B= isnull( B, @B ), 
             @C= C= isnull( C, @C ),
             @A=A+1
        from Tbl
        where A=@A
    end
    its a mix of your first select and my variable-version. your select works fine too, but it is much more expensive than the one above.

    thanks to all for your time
    markus

  14. #14
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Yes but the number of iterations will be equal to nuimber of rows in the table.
    --
    kukuk

  15. #15
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Originally posted by kukuk
    Yes but the number of iterations will be equal to nuimber of rows in the table.
    true, but i don't care as long as it runs much faster.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •