update query with multiple criterias

[eugzl]

Hi All.
I have couple update queries for the same table:

UPDATE Table1 SET Table1.Code = "01"
WHERE (((Table1.Code) Not In ("01","05","06","07","08")));
and
UPDATE Table1 SET Table1.ANum = Null
WHERE (((Table1.Group) Like "HC*"));

Is it possible to combine those queries into one update query? If yes. How to do it?

Thanks.

 

[Ranman256]

run 2 queries. don't cost nuthin.

 

[eugzl]

run 2 queries. don't cost nuthin.

Hi Ranman256. Thanks for reply.
Will macros run faster if you combine several similar queries into one?
Thanks

 

[eugzl]

It is possible by using IIf()'s instead of where clauses. However, this would be much less efficient since it would force the query engine to examine every single row in the table RBAR (Row by agonizing row, ie slowly)

I don't use macros. I use VBA but it doesn't matter. A query takes as much time as it takes. They are run serially no matter what method you use so the first has to complete before the second starts. There is no way to initiate a parallel process where the both run at the same time.

Hi Pat. Thanks for reply.
That is exactly what I want to know. Just one more question. What execute faster query code in VBA or query as DB object?
Thanks.

 

[CJ_London]

What execute faster query code in VBA or query as DB object?

if you are building the query sql in vba there will be a very small overhead as a query plan is created - I would say a matter of a few milliseconds

if you are executing an existing query from vba - then no difference from running it from the navigation window

for one query you could try

UPDATE Table1 SET Table1.Code = iif(Code Not In ("01","05","06","07","08"), "01",Code), Table1.ANum = iif(Group Like "HC*",Null,Anum)
WHERE Code Not In ("01","05","06","07","08")) OR Group Like "HC*"