Home Forums SQL Server 2005 T-SQL (SS2K5) Updaing using ROW_NUMBER() RE: Updaing using ROW_NUMBER()

  • March 11, 2013 at 9:48 am

    #1595633

    You don't need ROW_NUMBER here:

    declare @table table (ID int, Name varchar(100))

    insert @table select 1,null

    insert @table select 2,null

    insert @table select 3,'Bill'

    insert @table select 4,null

    insert @table select 5,null

    insert @table select 6,null

    insert @table select 7,null

    insert @table select 8,null

    insert @table select 9,'Ted'

    insert @table select 10,null

    insert @table select 11,null

    insert @table select 12,null

    UPDATE u

    SET u.Name = n.Name

    FROM @table u

    CROSS APPLY(SELECT TOP 1 t.Name FROM @table t

    WHERE t.Name IS NOT NULL

    AND ( t.ID - u.ID between 1 AND 2

    OR u.ID - t.ID between 1 AND 3)) n

    WHERE u.Name IS NULL

    SELECT * FROM @table

    Please refer to the link at the bottom of my signature. The article provides tips about how to post your question on this forum in order to get most relevant and prompt help.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]