‘Delete ‘ with Alias in T-SQL

Last week I was working on something where I was required to write a delete query against few records in table. Now a days I am kind of habituated to use alias for tables whenever I write any query (mostly because of tool I am we are using for SQL development, which don’t have intellisence if alias is not being used for table name !!) and in other case it was join between two tables was required to delete records. So as with other query, I wrote my simple delete query with alias … and found that when you use alias delete statement is a bit different !!! Below is how I reproduced same issue in my laptop. (Note: Query is using a test table I created using good ol’ AdventureWorks sample database)

-- Delete from table using Alias
Delete from TableFoo TF where City='Renton'

But parsing this query gave me following error

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ‘TF’.

So I have to re-write the same query,

-- Delete without Alias
-- Ignore "Transaction" part, I don't want to actually remove data 
-- So I am creating transaction and rolling it back to preserve my data

begin transaction

Delete TF from TableFoo TF where City='Renton'

rollback transaction

Anyways, I guess there are many things that we just don’t learn by reading but by actually working with them in field. And this is one of that.

That’s it for now.

It’s Just A Thought … Fingers crossed


Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *