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#);
views:
129answers:
2
+7
A:
no. the first one is not actually a 'query'. it is a statement of Data Definition Language
catwalk
2009-12-07 12:19:00
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
2009-12-07 15:21:40
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
2009-12-08 03:52:39
@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
2009-12-08 08:36:13
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
2009-12-08 22:44:39
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
2009-12-08 22:46:26
onedaywhen
2009-12-09 08:48:35
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
2009-12-07 15:30:54
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
2009-12-08 03:57:34
@gbn "not SQL" -- of course it's SQL: Access Database Engine ANSI-92 Query Mode SQL DDL but its still SQL.
onedaywhen
2009-12-08 08:32:49
ah, I've not worked with Access like this. And did not know about PROCEDUREs in Access either.
gbn
2009-12-08 19:12:42
It's not the kind of sproc you're accustomed to -- there's no procedural code allowed.
David-W-Fenton
2009-12-08 22:47:04