views:

129

answers:

2

Can I combine these two SQL queries into single query?

query1

ALTER TABLE tableA
ADD datam INTEGER;

query2

UPDATE tableA SET datam = DateDiff("m",[call_date],#12/1/2009#);
+7  A: 

no. the first one is not actually a 'query'. it is a statement of Data Definition Language

catwalk
Agreed but note that this sloppy usage of 'query' is endemic in the Access documentation, particularly 'update query' to mean an UPDATE DML statement and 'delete query' for DELETE DML statement (plus 'action query' collectively!) Also note that you persist the SQL in the Access Database Engine, the resulting object will appear in the 'Queries' tab in the UI.
onedaywhen
This is Access's terminology. You don't have to like it, but it's the way the objects are presented in the UI for ease of understanding by people who are not SQL-heads. You may not agree with MS's decision (made in the early 90s, before SQL 92 was even out) to use terminology in this way, but it is not going to help Access users to have you (or anyone else) quibbling about it at every opportunity. People will just tune you out as the distinction you ask people to make has little utility to them.
David-W-Fenton
@David W. Fenton: you attacking me or the six (and counting) other people who up-voted this 'quibbling' answer plus the OP who chose it as the answer? ;) Read back what I wrote and you may see that I was coming to the OP's defence.
onedaywhen
Who is attacking? You use the word "endemic" which has a negative connotation, so you clearly don't consider it a good usage.
David-W-Fenton
The reason the two can't be combined into one statement is not because the first one is a DML statement but because Access/Jet/ACE cannot process multiple SQL statements separated by the ; delimiter. While the answer doesn't explicitly claim that the reason for the NO answer is because it's DML, it's implied, and that's an incorrect explanation. Really it's more a terminological quibble that has nothing to do with the actual answer to the question.
David-W-Fenton
onedaywhen
A: 

You could create a help PROCEDURE to add the calculated default e.g. (ANSI-92 Query Mode Access Database Engine SQL syntax):

CREATE PROCEDURE CreateTableAThing
(
 arg_my_key_column CHAR(10), 
 arg_call_date DATETIME
)
AS 
INSERT INTO tableA (my_key_column, call_date, datam)
SELECT arg_my_key_column, arg_call_date, DATEDIFF('m', [arg_call_date], #2009-12-01 00:00:00#)
  FROM MyOneRowAuxilliaryTable;

You could also create a helper functions for UPDATE perhaps again to maintain the default.

You could then remove INSERT/UPDATE privileges from the table to force all applications and users to go via your helper procs to ensure the default is correctly applied.

onedaywhen
it's for MS Access, not SQL. We're all making the same mistake...
gbn
In SQL 92 mode, Access will excecute @onedaywhen's SQL. He should have noted the requirement for SQL 92 mode in his posting, but in my experience, he's not one who posts SQL that won't run in Access.
David-W-Fenton
@gbn "not SQL" -- of course it's SQL: Access Database Engine ANSI-92 Query Mode SQL DDL but its still SQL.
onedaywhen
ah, I've not worked with Access like this. And did not know about PROCEDUREs in Access either.
gbn
It's not the kind of sproc you're accustomed to -- there's no procedural code allowed.
David-W-Fenton