Seb Seb -4 years ago 146
SQL Question

Optimize SQL query in VBA

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 :


  1. Adding 2 INNER JOIN but I get a syntax error 3075

  2. 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

  3. This was my previous solution using 2 SELECT statements but I
    got recommended to use JOIN instead



Any suggestions welcomed !

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download