Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

Queries: Jet-MSDE: DELETE * FROM Table1; Incorrect syntax near '*'

Author(s)
Michel Walsh &
John Viescas

Translations of DELETE * FROM Table, and DISTINCTROW, from JET 4, to MS SQL Server 7, or MSDE.

For a JET user, the SQL statement produced by the Query designer for a delete statement looks like:

DELETE * FROM Table1 WHERE ...

but that same SQL statement, in MS SQL Server 7, or MSDE, will just produce an error, even if you don't use a WHERE clause:

Incorrect syntax near '*'

In fact, the required syntax, for MS SQL Server, is: 

DELETE FROM Table1 WHERE ...

since you delete the whole record, and not just "some" field.

But wait,... is that all? What about the case we have an inner join, since we can only delete from a single, one, table, from which table will we delete in the expression:

DELETE FROM Table1 INNER JOIN Table2 ON ....

As you probably know, in Jet 4, the syntax is: 

DELETE DISTINCTROW Table1.* FROM Table1 INNER JOIN Table2 ON ....

but DISTINCTROW is a keyword known only in Jet. To translate the same idea, in MS SQL Server, we can use the special (not a standard SQL) DELETE FROM FROM syntax:

DELETE FROM Table1 FROM Table1 INNER JOIN Table2 ON ....

where we have just replaced the "DISTINCTROW Table1.*" to "FROM Table1"  and the conversion from Jet 4 to MS SQL Server 7 has been done.

If you want to go with strictly ANSI Standard SQL in either one, use this  equivalent:

DELETE FROM Table1  WHERE Table1.Key1 IN (SELECT Key1 FROM Table2) ...

[Don't forget to put Table1.* between DELETE and FROM in the JET version!]

Since we speak of DISTINCTROW, that keyword can also be implied, with Jet 4, with updates:

UPDATE DISTINCTROW Table1     
   INNER JOIN Table2     
   ON Table1.F1 = Table2.F1     
   SET Table1.F2 = 44;

A possible translation for MS SQL Server and MSDE is, in this case:

UPDATE Table1 
    SET Table1.F2=44 
    FROM  Table2 
    WHERE Table1.F1=Table2.F1

Have fun with your future translation.

References:

168438
ACC97: Changes with DISTINCTROW in Microsoft Access 97
Q163054
ACC97: Error Running Update Query in Converted Database
Transact-SQL Programming
O'Reilly, by Kline, Gould and Zanevsky

pp108-112

© 1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer