r-在SQL Server中转换MS Access更新查询

Question

我目前正在构建一个基于以前的MS Access应用程序的Shiny应用程序。我需要在SQL Server中的每个Shiny应用程序按钮后面复制MS Access查询。在R中重用MS Access中的SQL语法的最佳方法是什么(即，直接在R中复制粘贴MS Access查询(？

事实上，Access SQL似乎与SQL Server语法略有不同，因此我不能简单地使用DBI(dbGetQuery()、dbExecute()、dbSendQuery()(或dbplyer(sql()(。

这里有一个R中MS Access SQL语法的例子("100%"是故意留下的，因为表名包含该字符串。(

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));

在R控制台中导致以下错误消息：

错误：nanodbc/nanodbc.cpp:1617:42000:[Microsoft][ODBC SQL Server Driver][SQL Server]关键字'INNER'附近的语法不正确。[Microsoft][ODBC SQL Server驱动程序][SQL Server:"table3"附近的语法不正确。无法准备[Microsoft][ODBC SQL Server驱动程序][SQL Server]语句。

我还得到："；多部分标识符…无法绑定到Join语句"中；当我调整查询时。

我设置了与的连接

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

其中参数。SQL服务器="；SQL Server">

Answer 1

除了UPDATEE...FROM翻译外，还应考虑避免像MS Access那样嵌套JOIN。具体而言，将所有ON子句移到JOIN子句之后，如果所有表都与INNER JOIN组合，则这些子句应该有效。实际上，您甚至可以将WHERE条件移动到ON。

还可以考虑表别名，以便于短期阅读。最后，通常更新的表列应该在FROM子句中。此外，如果table1和table4不用于过滤，则它们是冗余的。

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]

Answer 2

Access有一个非常奇怪和非典型的UPDATE语法，它具有联接，允许它同时更新多个表并合并数据(向表中添加行(。

SQL Server有一种不同的语法，在这种语法中，您需要明确要更新的表。

然而，重写很简单：

• 在UPDATE关键字后列出要更新的表。

• 将包括该表在内的所有联接移动到FROM子句中。

• 此外，SQL Server中没有感叹号而不是句点，但为了兼容性，我建议您在Access中也不要使用这些感叹号。

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));