DELETE FROM foo; and DELETE * FROM foo;

[Banana]

I just discovered that Jet derive from standard SQL. In standard SQL, it makes no sense to specify columns in DELETE query because an entire row get deleted as per the definition of delete query and if only partial deletion of columns' data was desired, a UPDATE query is actually appropriate.

But apparently it is valid to write this in Jet:

DELETE * FROM foo;
DELETE a, b, c FROM foo;

Furthermore, if I write this in SQL view:

DELETE FROM foo;

I can execute the SQL just fine. But I can't view the rows that will be deleted; I get an error saying must have at least one destination field.

What's the deal with this? I'm guessing it has something to do with being able to select/view columns in datasheet view without actually executing the SQL, but this still looks weird and downright confusing.

 

[johncallaghan]

I use DoCmd.RunSQL "DELETE * FROM foo"
and this works fine

Cheers

John

 

[Atomic Shrimp]

What's the deal with this? I'm guessing it has something to do with being able to select/view columns in datasheet view without actually executing the SQL, but this still looks weird and downright confusing.

I suspect that's part of the reason. Another part is probably that the visual query builder needs some fields selected to be able to represent the SQL in graphical form.