|
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:
|
