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
Advertisements
This entry was posted in Software Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s