468,526 Members | 2,097 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,526 developers. It's quick & easy.

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

NeoPa
32,098 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM   [DBManagement].[dbo].[AnalysisRange] AS [tAR] INNER JOIN
  3.        [DBManagement].[dbo].[AnalysisRange] AS [tAR2]
  4.   ON   SUBSTRING([tAR].[AR_Analysis],2,3)=SUBSTRING([tAR2].[AR_Analysis],2,3)
  5.  AND   [tAR].[AR_Range]=[tAR2].[AR_Range]
  6. WHERE  SUBSTRING([tAR2].[AR_Analysis],1,1)='1'
  7. GO
Just to be crystal clear, I'm talking about replacing line 1 with :
Expand|Select|Wrap|Line Numbers
  1. 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]
Expand|Select|Wrap|Line Numbers
  1. [AR_Analysis]  [AR_Range]
  2.     0401           21
  3.     0401           22
  4.     0401           23
  5.     0420           20
  6.     0420           22
  7.     0420           24
  8.     1401           22
  9.     1420           24
The valid data left after the process is complete should be :
Expand|Select|Wrap|Line Numbers
  1. [AR_Analysis]  [AR_Range]
  2.     0401           21
  3.     0401           23
  4.     0420           20
  5.     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 :
Expand|Select|Wrap|Line Numbers
  1. Msg 156, Level 15, State 1, Line 2
  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.
Jul 26 '10 #1

✓ answered by 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 :)
Expand|Select|Wrap|Line Numbers
  1. USE [DBManagement]
  2. GO
  3.  
  4. DELETE [tAR]
  5. FROM   [dbo].[AnalysisRange] AS [tAR] INNER JOIN
  6.        [dbo].[AnalysisRange] AS [tAR2]
  7.   ON   SUBSTRING([tAR].[AR_Analysis],2,3)=SUBSTRING([tAR2].[AR_Analysis],2,3)
  8.  AND   [tAR].[AR_Range]=[tAR2].[AR_Range]
  9. WHERE  SUBSTRING([tAR2].[AR_Analysis],1,1)='1'
  10. GO
Thank you for making me look more deeply at the Help explanation. It was hard work but worth it :)

5 5088
Oralloy
983 Expert 512MB
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.
Jul 27 '10 #2
NeoPa
32,098 Expert Mod 16PB
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 :)
Expand|Select|Wrap|Line Numbers
  1. USE [DBManagement]
  2. GO
  3.  
  4. DELETE [tAR]
  5. FROM   [dbo].[AnalysisRange] AS [tAR] INNER JOIN
  6.        [dbo].[AnalysisRange] AS [tAR2]
  7.   ON   SUBSTRING([tAR].[AR_Analysis],2,3)=SUBSTRING([tAR2].[AR_Analysis],2,3)
  8.  AND   [tAR].[AR_Range]=[tAR2].[AR_Range]
  9. WHERE  SUBSTRING([tAR2].[AR_Analysis],1,1)='1'
  10. GO
Thank you for making me look more deeply at the Help explanation. It was hard work but worth it :)
Jul 27 '10 #3
Oralloy
983 Expert 512MB
@NeoPa
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
Jul 27 '10 #4
NeoPa
32,098 Expert Mod 16PB
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.
Jul 27 '10 #5
Oralloy
983 Expert 512MB
@NeoPa
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.
Jul 27 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jeff Magouirk | last post: by
11 posts views Thread by Mark Findlay | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.