ansaurus

Question

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

Answer 1

A:

Because they don't know what they're doing??

Fermin 2010-01-21 12:54:37

Did you ever stop to think that is your ignorance here that explains why you don't see a reason for this syntax? A good reason is when typing in sql by free hand if you include a few columns then you can flip from freehand sql into datasheet view mode and you will see the records that will be deleted when query is run. I thus include PK and a few columns to CLEARLY display the 3 records expected out of 200000 in the table. If the visual display is correct then I simply whack the ! to run the delete sql. Thus tip means adding columns allows preview of data before you actually delete it.

Albert D. Kallal 2010-01-21 16:27:11

Most other people don't see a reason either friend, so we must all be ignorant. You mean that you write a select statement to check which data you're going to delete? We all do that, you'd be mad not to. Just most of us then comment out the 'select col1, col2.. etc' and replace with 'delete'.

Fermin 2010-01-21 16:55:49

@Fermin: you seem to be posting in ignorance of the Access UI, which offers a lot of tools that those who have to hand-write their SQL don't have available to them.

David-W-Fenton 2010-01-21 20:13:27

Fermin: You took the first shot at someone being ignorant here so why is it not appropriate to throw that right back at you? You are suggesting to write a WHOLE NEW select statement as a good practice to check what the delete sql will do. Your suggestion is not only extra work but also subject to introducing errors. Modifying an existing select with edits or even cut + paste into an delete is STILL prone to introducing NEW errors. With the feature I outlined here you DO NOT have to write or even edit the delete statement ONCE you visually verified it has the data you want to delete.

Albert D. Kallal 2010-01-22 11:47:33

I'm not suggesting writing a WHOLE NEW statement. I propose writing the select statement to see that the rows returned are those to be deleted, putting a /**/ round the select statement and replacing it with the word 'delete'. I'm just saying there's no need to have column names in a delete statement.

Fermin 2010-01-22 12:43:38

"There's no need" != "they don't know what they're doing".

David-W-Fenton 2010-01-22 22:55:24

You still talking about additional editing of the sql statement. You still talking about having to write an select statement. You still talking about possibility of things being messed up when you are commenting out the select part + the columns part (you might for example mess up the closing delimiter for comments when you do this). Why bother with further edits and further commenting out of sql when you don't have to? This is not a case of column names not being needed in the delete statement but is simply a question of an supported feature and technique you were not aware of.

Albert D. Kallal 2010-01-23 01:53:13

Answer 2

+1 A:

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.

Mitch Wheat 2010-01-21 12:56:58

Thanks Mitch, I expected that was the case, and didn't even think about the fact that it was probably generated by Access. Thanks!

SeanW 2010-01-21 13:07:44

Not generated by Access so much as by the QBE. If you start in the QBE with a SELECT statement to insure that you're selecting the right records and then convert it to a delete, it will retain the fields in the original SELECT statement. I think this is just sloppiness, as I would always convert to the more standard DELETE * (I don't like the DELETE FROM syntax, even though it's supported, because it just feels wrong).

David-W-Fenton 2010-01-21 20:11:59

Answer 3

A:

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.

FireAphis 2010-01-21 12:59:10

Answer 4

+1 A:

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;

Michel 2010-01-21 14:20:09

ansaurus

tags:

views:

answers:

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

related questions