(MSSQL 2005) - Incorrect syntax near the keyword 'AS'

[NeoPa]

As the title implies, I am getting the following error message with some SQL I'm trying to use. I'm using MS Management Studio 2008 with a link to a 2005 server.

What confuses me is that when I change the first line from a DELETE to a SELECT, the SQL works exactly as I would expect it to.

DELETE
FROM   [DBManagement].[dbo].[AnalysisRange] AS [tAR] INNER JOIN
       [DBManagement].[dbo].[AnalysisRange] AS [tAR2]
  ON   SUBSTRING([tAR].[AR_Analysis],2,3)=SUBSTRING([tAR2].[AR_Analysis],2,3)
 AND   [tAR].[AR_Range]=[tAR2].[AR_Range]
WHERE  SUBSTRING([tAR2].[AR_Analysis],1,1)='1'
GO

Just to be crystal clear, I'm talking about replacing line 1 with :

SELECT [tAR].*, [tAR2].*

Background :

I have a table consisting of two fields, [AR_Analysis] (nvarchar(4)) and [AR_Range] (smallint). Each record is unique but neither field is. Valid values in [AR_Analysis] start with a '0' and are 4 characters long. I have the table populated currently with various matching pairs, but I have a new list of pairs within that list which should be deleted. To effect this I added the pairs to be deleted into the table but with a '1' as the first character of [AR_Analysis] but otherwise the same. I intend to clear away the original invalid items, as well as the new deletion items (starting with '1') with the above SQL. Let me see if I can make this easier with some example data :
Table = [AnalysisRange]

[I][AR_Analysis]  [AR_Range][/I]
    0401           21
    0401           22
    0401           23
    0420           20
    0420           22
    0420           24
    1401           22
    1420           24

The valid data left after the process is complete should be :

[I][AR_Analysis]  [AR_Range][/I]
    0401           21
    0401           23
    0420           20
    0420           22

The strange part, that completely confuses me, is that the results produced by what is essentially the same SQL when used as a SELECT query are exactly as I'd expect. When the DELETE query is attempted though, I get the message :

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.

I have checked in the Books Online help system, but wasn't able to decipher an answer in it. Maybe I need to get more used to the terminology, but for now it's too obscure for me.

PS. Any advice as to alternative ways of approaching my main problem is welcomed (I'm sure I'm not using the best approach) but I'm mainly interested in the specific question as to why what I'm attempting is causing this response.

[Oralloy]

Reading microsoft's documentation here, I see no reference to table aliases (the "AS") in their syntax diagram.

I didn't see anything similar for MySQL, either.

I'd be willing to bet that you have to use the fully qualified table names in your query.

[NeoPa]

Thanks for the link Oralloy. I'm afraid the idea wasn't quite right, but the link helped nevertheless.

If you look at the last example under Example D, you'll see aliases used. It does get a little complicated though. There seem to be two different FROM clauses (unless maybe the first, on the first line, is more of a predicate than a clause. I'm not sure I know). Anyway, this first FROM seems to have the job of specifying where the deletions should occur from. It's possible this has no facility to use an alias as you say. The second, which seems to be specifying the source of data that identifies the records in the first that need to be deleted, certainly seems to have the facility to use aliases. This query would not be possible to specify otherwise as the same table is used twice in two different ways.

All that said, I think I do have a much better understanding of the DELETE clause now. When I tried to look at the specification yesterday I missed the bit where there were two FROMs. I must have got confused by all the bracketing and assumed they were simply in different optional components (Most of my SQL experience is in Jet where the format of the DELETE clause is quite different).

The following SQL worked perfectly :)

USE [DBManagement]
GO

DELETE [tAR]
FROM   [dbo].[AnalysisRange] AS [tAR] INNER JOIN
       [dbo].[AnalysisRange] AS [tAR2]
  ON   SUBSTRING([tAR].[AR_Analysis],2,3)=SUBSTRING([tAR2].[AR_Analysis],2,3)
 AND   [tAR].[AR_Range]=[tAR2].[AR_Range]
WHERE  SUBSTRING([tAR2].[AR_Analysis],1,1)='1'
GO

Thank you for making me look more deeply at the Help explanation. It was hard work but worth it :)

[Oralloy]

Thanks for the link Oralloy. I'm afraid the idea wasn't quite right, but the link helped nevertheless.

If you look at the last example under Example D, you'll see aliases used. It does get a little complicated though. There seem to be two different FROM clauses (unless maybe the first, on the first line, is more of a predicate than a clause. I'm not sure I know). Anyway, this first FROM seems to have the job of specifying where the deletions should occur from. It's possible this has no facility to use an alias as you say. The second, which seems to be specifying the source of data that identifies the records in the first that need to be deleted, certainly seems to have the facility to use aliases. This query would not be possible to specify otherwise as the same table is used twice in two different ways.

All that said, I think I do have a much better understanding of the DELETE clause now. When I tried to look at the specification yesterday I missed the bit where there were two FROMs. I must have got confused by all the bracketing and assumed they were simply in different optional components (Most of my SQL experience is in Jet where the format of the DELETE clause is quite different).

The following SQL worked perfectly :)

USE [DBManagement]
GO

DELETE [tAR]
FROM   [dbo].[AnalysisRange] AS [tAR] INNER JOIN
       [dbo].[AnalysisRange] AS [tAR2]
  ON   SUBSTRING([tAR].[AR_Analysis],2,3)=SUBSTRING([tAR2].[AR_Analysis],2,3)
 AND   [tAR].[AR_Range]=[tAR2].[AR_Range]
WHERE  SUBSTRING([tAR2].[AR_Analysis],1,1)='1'
GO

Thank you for making me look more deeply at the Help explanation. It was hard work but worth it :)

Hey, I'm glad it helped. I learned something, too, which is why I participate here.

Other than convienence, is there a reason for the USE statement before your query?

Cheers,
Oralloy

[NeoPa]

Oralloy: Other than convienence, is there a reason for the USE statement before your query?

Absolutely not. You're right on the money (at least that was certainly my motivation).

I've done T-SQL before, but I'm mainly an Access man at the moment. I'm hoping to move into SQL Server more though, as time goes on. Some of the ideas seem quite different though.

[Oralloy]

Absolutely not. You're right on the money (at least that was certainly my motivation).

I've done T-SQL before, but I'm mainly an Access man at the moment. I'm hoping to move into SQL Server more though, as time goes on. Some of the ideas seem quite different though.

God, Access reports just give me fits. Good luck making the transition to SQL Server. I've always found my problems were with reporting and inherited de-normal schemata, and not with new design.

I'm really not familiar with SQL Server as its self, just as another form of SQL server over ODBC. I've used it and written basic schemata against it using portable SQL, but nothing hard-core microsoft centric (if that makes sense?).

Most of my database has been through ODBC from C++ and Java, with no use of stored procedures. Basically what I do is create schemata, create applications that log tons of measurement information, retrieve data, compute fits, store final working calibration information. All-in-all, just basic, simple, generic SQL.