THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Uri Dimant

Alias issue in T-SQL or defensive programming

Recently I have talked to our developer who wanted to delete TOP x rows from the table. I pointed him to the below artcile where a tip – a view with ORDER BY.

As alternative he wanted using a derived table but cannot understand why all rows are deleted from the table instead of TOP(x). See the below demo.

create table #t (c int)
insert into #t values (1)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)

delete #t from (select top (2) c
        from #t order by c) t

How does  DELETE extension in T-SQL work?. The FROM clause after the DELETE specifies the target table to delete. The second optional FROM clause specifies the qualifying rows. But if I change 't' alias to '#t' as original name  of the temporary table that would work...

delete #t from (select top (2) c
        from #t order by c) #t

Now, SQL Server 'sees' that derived table has the same name as a target and thus deletes only TOP(x) rows

There is no goal of this post to get into a discussion about how to write correlated subquery to perform such operations, I just wanted you to pay attention on if you choose using derived tables to perform deletion please make sure  that alias you specify for derived table is the same as a target table..

PS. If you are testing and not sure about the result please use BEGIN TRAN... before executing the script.If you see that rows affected by the script is too many issue ROLLBACK TRAN to back  to original data.


Published Thursday, January 20, 2011 12:21 AM by Uri Dimant



Peter said:

Or perhaps, just use the identifier after DELETE?

delete t from (select top (2) c from #t order by c) t

January 20, 2011 7:50 PM

Rob Farley said:

I recommend only using an alias before the FROM / SET clause whenever a DELETE/UPDATE ... FROM pattern is used. It's too easy to have the system think it's an unlisted table otherwise.

January 29, 2011 9:01 PM

Eric in Sac said:

I had a heck of a time translating this to a non-temp table, but at least I was able to figure it out, which is more than I can say for the DELETE example in Books Online.  Thanks again, Uri.  Here's my working statement:

DELETE dbo.MainRecords FROM  (

SELECT mr.* FROM dbo.MainRecords AS [mr]

INNER JOIN dbo.SecondaryRecords AS [sr]

ON mr.ClientId = sr.ClientId

AND mr.ActivityDate = sr.ActivityDate) MainRecordsTable

March 22, 2011 5:04 PM
New Comments to this post are disabled

About Uri Dimant

Uri Dimant
Privacy Statement