Optimize SQL query in VBA

Question

I am trying to find the most optimized way to do this operation which runs an SQL statement in Access-VBA :

SQl = "UPDATE " _
& "MainTable As T1 " _
& "INNER JOIN TableOfLists As T2 " _
& "ON (T2.SecondList = T1.MultiValuedList.value) " _
& "Set [FOUND IN LISTS] = 'YES' "
DoCmd.RunSQL SQl

SQl = "UPDATE " _
& "MainTable As T1 " _
& "INNER JOIN TableOfLists As T2 " _
& "ON (T2.FirstList = T1.MultiValuedList.value) " _
& "Set [FOUND IN LISTS] = 'YES' "
DoCmd.RunSQL SQl

This code works, can be improved surely, but I didn't manage to find out how.

What I've tried so far and the results I got :

Adding 2 INNER JOIN but I get a syntax error 3075
Adding 2 conditions separated by an OR in the INNER JOIN condition but I get an error 3081 : can't join more than 1 table
This was my previous solution using 2 SELECT statements but I got recommended to use JOIN instead

Any suggestions welcomed !

do you mean On ((T2.SecondList = T1.MultiValuedList.value)) Or (T2.FirstList = T1.MultiValuedList.value) ???? — Tony Hopkinson, Feb 28 '17 at 17:01
Access is telling you that multi-valued fields are evil. http://stackoverflow.com/questions/1461582/multivalued-fields-a-good-idea — Andre, Feb 28 '17 at 18:28
i would convert that to an actual query, and just run the query unless some of the objects you reference are built dynamically. without knowing the volume and and indexes on your tables it won't help to comment on it — O. Gungor, Feb 28 '17 at 20:49

score 3 · Accepted Answer · edited Nov 13 '18 at 23:20

Here you go. This is kind of the ANSI SQL way of doing things, because joins within an UPDATE are not supported. (See this blog entry for more detail.) This is why you saw an error in your first approach (with two INNER JOINs, because Access/Jet is giving you a special feature in their UPDATE syntax, but it's not as fully-developed as vanilla SQL is. Your second approach (with an OR in the join condition) errors out because Access/Jet's support for conditions in join criteria is very limited (you wouldn't see this in Oracle or Postgres, for example). And it turns out your third approach (with two selects, but using IN instead of EXISTS) is the same, under the hood. So whoever told you to use joins was ill-informed :).

UPDATE MainTable SET [FOUND IN LISTS]='YES'
WHERE 
    EXISTS (SELECT 1 FROM TableOfLists WHERE FirstList=MainTable.[value])
    OR
    EXISTS (SELECT 1 FROM TableOfLists WHERE SecondList=MainTable.[value]);

Example in Access

Thanks a lot for this answer ! Exactly what I was looking for :) — Seb, Mar 01 '17 at 14:45

Optimize SQL query in VBA

1 Answers1