combining queries in sql

[Danny Boukhris]

I have 20 update queries that I want to combine on one
query. I have to run all 20 one at a time. How can I
combine these queries.

Here are 3 of the 20 update queries. I went to one query
and selected show sql, then I copied and pasted, but I am
getting error messages. The syntax definitely must be
wrong, I spend time adusting the parenthesis but no luck.
Please assist in the syntax where I can put all 20 sql
statements on a single update query.
Thanks!

QUERY #1
UPDATE TEST SET TEST.UNIT = 101
WHERE (((TEST.USERID) Like "C11*"));

QUERY #2
UPDATE TEST SET TEST.UNIT = 102
WHERE (((TEST.USERID) Like "C12*"));

QUERY #3
UPDATE TEST SET TEST.UNIT = 103
WHERE (((TEST.USERID) Like "C13*"));

 

[Brian Camire]

In Access, you cannot execute multiple SQL statements in a single query.
You must execute each one separately. If you want the updates to be atomic
(either they all succeed or all fail), you can execute them (via the Execute
method of an ADO Connection or Command object, or via the Execute method of
a DAO Database or QueryDef object) within a transaction (via the
BeginTrans/CommitTrans methods ether an ADO Connection object or a DAO
Workspace object).

But, maybe you only need one statement.

If you had a table (say, named "PREFIXES") like this:

PREFIX, UNIT
"C11",101
"C12",102
"C13",103
..
..
..

Then you might be able to do something like this:

UPDATE
TEST,
PREFIXES
SET
TEST.UNIT = PREFIXES.UNIT
WHERE
TEST.USERID Like PREFIXES.PREFIX & "*";

This assumes the no USERID starts with more than one of the PREFIX values.

 

[Danny]

Had some trouble with this. The User id is 6 chars long.
C11??? is unit 101, and C21??? is unit 201.

I don't understand the ADO, DAO things you said.

 

[Brian Camire]

Set aside the ADO/DAO discussion for now.

I suspect you can do what you need using one query.

If the logic for coming up with the unit from the user ID (and I'm guessing
here, because you haven't spelled it out) is:

1. Take the second character of user ID and interpret as a digit, and
multiply it by 100, and,

2. Take the third character of the user ID, interpret it as a digit, and
add it to the result from Step 1,

then maybe it's even simpler that the table-based approach I suggested in my
previous post. You might try an update query whose SQL looks something like
this:

UPDATE
TEST
SET
TEST.UNIT = Mid(TEST.USERID,2,1)*100+Mid(TEST.USERID,3,1)

If the logic is more complex, you might try the table-based approach I
suggested in my previous post. In other words, you have a separate table
that lists the prefix (in this case, the first three characters) and the
corresponding unit, and then update your original table based on it.