Windows LiveWindows Live™
 
 
Sloan's profileGranada Coder, C#, Sql S...PhotosBlogLists Tools Help

Blog
    7/6/2009

    Update TOP (N) (ORDER BY) Example

     
    We all know a SELECT TOP(N) (ORDER BY) statement will work.
     

    Use Northwind
    GO
    select top(10) * from dbo.Orders ORDER BY CustomerID , OrderDate
    GO

    However, if you've tried to do a:
     
    Update TOP (10) ...... ORDER BY X
     
    That does not work.
     
    Here is a workaround example to do an Update TOP(N) (ORDER BY) query with one (cte) statement.
     
     
     
    ----------START TSQL
     
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Television]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
          BEGIN
                DROP TABLE [dbo].[Television]
          END
    GO
     
     
    CREATE TABLE [dbo].[Television] (
          TelevisionUUID [uniqueidentifier] not null default NEWSEQUENTIALID() ,
          TelevisionName varchar(64) not null ,
          TelevisionKey int not null ,
          IsCheckedOut bit default 0
    )    
    GO
     
     
    ALTER TABLE dbo.Television ADD CONSTRAINT PK_Television_TelevisionUUID
    PRIMARY KEY CLUSTERED (TelevisionUUID)
    GO
     
     
    ALTER TABLE dbo.Television ADD CONSTRAINT CK_Television_TelevisionName_UNIQUE
    UNIQUE (TelevisionName)
    GO
     
     
    set nocount on
     
    declare @counter int
    select @counter = 11000
    declare @currentTVName varchar(24)
    declare @TopSize int
    select @TopSize = 10
     
    while @counter > 10000  -- this loop counter is ONLY here for fake data,....do not use this syntax for production code
    begin
     
          select @currentTVName = 'TV:     '+ convert(varchar(24) , @counter)
     
          INSERT into dbo.Television ( TelevisionName , TelevisionKey ) values ( @currentTVName , @counter)
     
          select @counter = @counter - 1     
    end
     
     
    select count(*) as TV_Total_COUNT from dbo.Television
     
    /*
    --Does not Work!
    Update TOP (10) dbo.Television
          Set IsCheckedOut = 1
    FROM
          dbo.Television tv
    ORDER BY tv.TelevisionKey
    */
    declare @AuditTrail table ( TelevisionUUID uniqueidentifier , OldIsCheckedOut bit , NewIsCheckedOut bit )
     
    ;
    WITH cte1 AS      
     (  SELECT
          TOP (@TopSize)
      
       TelevisionUUID , --<<Note, the columns here must be available to the output
       IsCheckedOut       
          FROM  
                dbo.Television tv     
        WITH ( UPDLOCK, READPAST , ROWLOCK ) --<<Optional Hints, but helps with concurrency issues  
          WHERE 
                IsCheckedOut = 0             
          ORDER BY
                tv.TelevisionKey DESC       
    )
    UPDATE cte1
          SET  IsCheckedOut = 1
    output inserted.TelevisionUUID , deleted.IsCheckedOut , inserted.IsCheckedOut into @AuditTrail ( TelevisionUUID , OldIsCheckedOut , NewIsCheckedOut )
    ;
    print ''
    print 'Newly Checked Out Items'
    select * from dbo.Television tv where tv.IsCheckedOut <> 0
     
    print 'Output AuditTrail'
    select * from @AuditTrail
    print 'Not checked out items'
    select count(*) as TVCOUNTIsNOTCheckedOut from dbo.Television tv where tv.IsCheckedOut = 0
    2:01:22 PM | Blog it | Software Development

    Comments

    Please wait...
    Sorry, the comment you entered is too long. Please shorten it.
    You didn't enter anything. Please try again.
    Sorry, we can't add your comment right now. Please try again later.
    To add a comment, you need permission from your parent. Ask for permission
    Your parent has turned off comments.
    Sorry, we can't delete your comment right now. Please try again later.
    You've exceeded the maximum number of comments that can be left in one day. Please try again in 24 hours.
    Your account has had the ability to leave comments disabled because our systems indicate that you may be spamming other users. If you believe that your account has been disabled in error please contact Windows Live support.
    Complete the security check below to finish leaving your comment.
    The characters you type in the security check must match the characters in the picture or audio.

    To add a comment, sign in with your Windows Live ID (if you use Hotmail, Messenger, or Xbox LIVE, you have a Windows Live ID). Sign in


    Don't have a Windows Live ID? Sign up

    Trackbacks

    The trackback URL for this entry is:
    http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!582.trak
    Weblogs that reference this entry
    • None