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#);
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#);
no. the first one is not actually a 'query'. it is a statement of Data Definition Language
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.