update query with multiple criterias (1 Viewer)

eugzl

Member
Local time
Yesterday, 19:02
Joined
Oct 26, 2021
Messages
100
Hi All.
I have couple update queries for the same table:
Code:
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.
 

eugzl

Member
Local time
Yesterday, 19:02
Joined
Oct 26, 2021
Messages
100
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:02
Joined
Feb 19, 2002
Messages
35,924
Is it possible to combine those queries into one update query? If yes. How to do it?
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.
 

eugzl

Member
Local time
Yesterday, 19:02
Joined
Oct 26, 2021
Messages
100
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

Super Moderator
Staff member
Local time
Today, 00:02
Joined
Feb 19, 2013
Messages
14,219
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

Code:
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*"
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:02
Joined
Feb 19, 2002
Messages
35,924
What execute faster query code in VBA or query as DB object?
As CJ said, the difference in speed for a single execution would be undetectable. The only time you might see a difference is if you were running the queries hundreds of times and that would give the extra overhead associated from running embedded SQL vs querydefs might be apparent.

Many experts turn up their noses at the QBE because of its flaws but I use it probably 99% of the time. The only time I ever use embedded SQL is if the SQL is dynamic. ie. I have to change the select or where clause based on user selections. A query that takes arguments is not dynamic. It is static because nothing structural changes and the saved execution plan does not need to be recalculated. It makes absolutely no difference to the query engine whether you are looking for CustID 9393 or CustID 101010. Dynamic SQL would be needed if the user wanted to decide at runtime whether he wanted the TOP 10 or TOP 5. There is no way to parametrize the number so you have to build the query dynamically unless you want to make two querydefs and run one or the other.
 

Users who are viewing this thread

Top Bottom