Convert an MS Access update query in SQL Server

Question

I am currently building a Shiny App that is based on a previous MS Access App. I need to replicate MS Access queries behind each Shiny app button in SQL Server. What is the best way to reuse the SQL syntax in MS Access in R (i.e., copy-paste the MS Access query directly in R)?

Indeed it appears that Access SQL is slightly different from SQL Server syntax and therefore I cannot simply do it using either DBI (dbGetQuery(), dbExecute(), dbSendQuery()) or dbplyr (sql()).

Here is an example with MS Access SQL syntax in R. ("100%" is left on purpose as a table name contains that string.)

UPDATE [table1] 
INNER JOIN ([table2 100%] 
 INNER JOIN ([table3]
  INNER JOIN table4 
   ON ([table3].[col1] = table4.[col1]) 
   AND ([table3].col2 = table4.col2)) 
  
    ON ([table2 100%].[col1] = [table3].[col1]) 
    AND ([table2 100%].[col2] = [table3].[col2])) 

     ON [table1].col1 = [table3].col1 
SET [table2 100%].[col2] = [table3]![col2]
WHERE ((([table3].[colY])<>0) AND (([table3].[colZ])=True));

Resulting in the following error message in R console :

Erreur : nanodbc/nanodbc.cpp:1617: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'INNER'. [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'table3'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

I also get : "The multi-part identifier … could not be bound in Join statements" when I tweak the query.

I set up my connection with :

con <- DBI::dbConnect(odbc::odbc(), driver = params.SQL.driver, 
    server = params.SQL.server, database = params.SQL.database, 
    encoding = params.SQL.encoding)

where params.SQL.server = "SQL Server"

user_1422451 · Answer

In addition to UPDATEE...FROM translation, consider avoiding the nesting of JOIN as MS Access tends to do. Specifically, move all ON clauses right after JOIN clauses which should work given all tables are combined with INNER JOIN. In fact, you can even move WHERE conditions to ON.

Consider also table aliases for short-hand readability. Finally, usually the table colum being updated should be in FROM clause. Also, if table1 and table4 are not used for filtering, they are redundant.

UPDATE [t2]                                 -- USING ALIAS
SET [t2].[col2] = [t3].[col2]
FROM [table2 100%] t2                       -- MOVED DUE TO SET COLUMN REFERENCE

INNER JOIN [table3] t3
  ON  [t3].[col1] = [t2].[col1]
  AND [t3].[col2] = [t2].[col2] 
  AND [t3].[colY] <> 0
  AND [t3].[colZ] = 1                       -- NO True CONSTANT IN SQL SERVER

INNER JOIN [table1] t1
  ON  [t1].[col1] = [t3].[col1]   

INNER JOIN table4 t4
   ON  t4.[col1] = [t3].[col1]
   AND t4.[col2] = [t3].[col2]

user_7296893 · Answer

Access has a quite curious and atypical UPDATE syntax with joins that allows it to update multiple tables simultaneously and merge data (add rows to tables).

SQL Server has a different syntax, where you need to be explicit about which table you're updating.

The rewrite, however, is simple:

List the table you're updating after the UPDATE keyword.
Move all the joins, including that table, to a FROM clause.
Also, no exclamation marks instead of dots in SQL Server, but I recommend you avoid these in Access as well for compatibility.

UPDATE [table1] 
SET [table2 100%].[col2] = [table3].[col2]
FROM [table1] 
INNER JOIN ([table2 100%] 
 INNER JOIN ([table3]
  INNER JOIN table4 
   ON ([table3].[col1] = table4.[col1]) 
   AND ([table3].col2 = table4.col2)) 
  
    ON ([table2 100%].[col1] = [table3].[col1]) 
    AND ([table2 100%].[col2] = [table3].[col2])) 

     ON [table1].col1 = [table3].col1 
WHERE ((([table3].[colY])<>0) AND (([table3].[colZ])=True));

Convert an MS Access update query in SQL Server

2 Answers