CODEWITHSUNDEEP
sqlms-access
SeanW
SeanW

Reputation: 2355

MS Access SQL DELETE - why would someone specify column names?

I'm having to support an Access .mdb file that someone else has written. One of the button functions in this .mdb calls out to delete some data in an external MSSQL database. All very straightforward, but this syntax isn't something I've seen before:

DELETE 
  tblEquipmentConnections.SourceEquip, 
  tblEquipmentConnections.EquipmentConnectionID
FROM tblEquipmentConnections
WHERE 
    tblEquipmentConnections.SourceEquip = [Forms]![frmEquipment]![EquipmentID];

Is that any different than this?

DELETE 
FROM tblEquipmentConnections
WHERE 
    tblEquipmentConnections.SourceEquip = [Forms]![frmEquipment]![EquipmentID];

I can't find a case where specifying specific columns does anything - but I don't spend much time in Access, so I'm not sure how different the SQL syntax is...

Thanks!

Upvotes: 5

Views: 4255

Answers (3)

Michel
Michel

Reputation: 726

I think the query has been built directly into Access query editor.

And generally we begin by building a select query. Then we change the query type from "Select query" to "Delete query". Then we display the query source by selecting "SQL Mode" where we copy / paste a sql statement like this one :

 DELETE qc_Boxes.idBox, qc_Boxes.idScreen, qc_Boxes.title
 FROM qc_Boxes;

Upvotes: 1

FireAphis
FireAphis

Reputation: 6790

This is absolutely redundant. The place between DELETE and FROM is used only when the deletion is performed based on a multi-table condition, but even in this case it contains table names and not field names. Also it can contain * which is also redundant. In MySQL, for example it's an incorrect syntax.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300719

Specifying the column names makes no difference. It's just an Access thing.

The reason they might be there is because Access used to generate DELETE statements that way (not sure if it still does).

The second form without columns names is obviously preferable.

Upvotes: 3

Related Questions