合并更新和选择SQL语句 [英] Combining Update & Select SQL statements

问题描述

创建SQL语句的MS Access VBA脚本：

MS Access VBA Scripts that create SQL Statements:

我有一个SQL语句，该语句在新表SATable中派生两个字段：

I have a SQL Statement that derives two fields in a new table SATable:

SQLScript = "SELECT [DATA OUTPUT TABLE].* INTO SATable"
SQLScript = SQLScript & " FROM [DATA OUTPUT TABLE]"
SQLScript = SQLScript & " WHERE ((([DATA OUTPUT TABLE].[Journal Voucher ID]) In (SELECT [DATA OUTPUT TABLE].[Journal Voucher ID]"
SQLScript = SQLScript & " FROM [DATA OUTPUT TABLE]"
SQLScript = SQLScript & " GROUP BY [DATA OUTPUT TABLE].[Journal Voucher ID]"
SQLScript = SQLScript & " HAVING (((First([DATA OUTPUT TABLE].dBranch))=" & Chr(34) & "Navy" & Chr(34) & ") AND ((Last([DATA OUTPUT TABLE].dBranch))=" & Chr(34) & "USMC" & Chr(34) & ")) OR (((First([DATA OUTPUT TABLE].dBranch))=" & Chr(34) & "USMC" & Chr(34) & ") AND ((Last([DATA OUTPUT TABLE].dBranch))=" & Chr(34) & "Navy" & Chr(34) & "));)));"
DoCmd.RunSQL SQLScript

然后我有一条语句接受了这两个派生字段并追加它们到原始表上：

Then I have a statement that takes those two derived fields and appends them onto the original table:

DoCmd.RunSQL "UPDATE SATable INNER JOIN [DATA OUTPUT TABLE] ON (SATable.[Journal Voucher ID] = [DATA OUTPUT TABLE].[Journal Voucher ID]) AND (SATable.dBranch = [DATA OUTPUT TABLE].dBranch) SET [DATA OUTPUT TABLE].[Shared Appropriations] = [SATable].[dBranch] & " & Chr(34) & " Shared Appropriation" & Chr(34) & ";"

我的目标是删除SATable并在一条语句中执行此操作。

My goal is to remove the SATable and perform this action in one statement.

我以前一直在努力解决类似这样的问题，所以除了提供答案外，您还可以提供一些解释-教男人钓鱼！

I've struggled with problems like this before so instead of just providing an answer can you please provide some explanation - TEACH A MAN TO FISH!

干杯，
-E

Cheers, -E

编辑：---------------------- ---------------------------------------
数据输出表具有： / p>

------------------------------------------------------------- The Data Output Table has:

JVID dBranch SA
   1  Navy   N/A
   2  Navy   N/A
   3  Navy   N/A
   4  Navy   N/A
   A  USMC   N/A
   B  USMC   N/A
   3  USMC   N/A
   4  USMC   N/A

因此SATable变为：

So the SATable becomes:

JVID dBranch SA
   3  Navy   Navy SA
   3  USMC   USMC SA
   4  Navy   Navy SA
   4  USMC   USMC SA

然后更新查询将更新SA字段，默认情况下为N / A

Then the update query updates the SA field, which is N/A by default

JVID dBranch SA
   1  Navy   N/A
   2  Navy   N/A
   3  Navy   Navy SA
   4  Navy   Navy SA
   A  USMC   N/A
   B  USMC   N/A
   3  USMC   USMC SA
   4  USMC   USMC SA

推荐答案

好的，所以我可能要做一两个假设，但是看来您真正想要做的是加入 [数据输出表] （ @tDataOutputTable 在我自己的脚本中）（类似于您的操作），并且仅更新与 JVID 匹配的记录，但是不是 dBranch

Okay, so I might be making an assumption or two, but it looks like what you really want to do is to join [DATA OUTPUT TABLE] (@tDataOutputTable in my script) on itself (similar to what you're doing) and only update records that match on JVIDs, but not dBranch

现在，这将匹配 dBranch 列不匹配。如果您只希望在海军和 USMC的dBranch中发生这种情况，那么您将需要替换我的 d.dBranch<> d2.dBranch 和其他类似的东西。

Now, this will match any record where the dBranch columns don't match. If you only want this to happen for dBranch of 'Navy' and 'USMC', then you'll want to replace my d.dBranch <> d2.dBranch with something more like what you have.

这是我创建的用于测试它的SQL脚本：

Here is a SQL script that I created to test it:

DECLARE @tDataOutputTable TABLE
(
    JVID varchar(max),
    dBranch varchar(max),
    SA varchar(max)
)

INSERT INTO @tDataOutputTable (JVID, dBranch, SA)
VALUES 
('1', 'Navy', 'N/A'),
('2', 'Navy', 'N/A'),
('3', 'Navy', 'N/A'),
('4', 'Navy', 'N/A'),
('A', 'USMC', 'N/A'),
('B', 'USMC', 'N/A'),
('3', 'USMC', 'N/A'),
('4', 'USMC', 'N/A')

UPDATE d
    SET d.SA = d.dBranch + ' SA' -- or 'Shared Appropriation'
FROM @tDataOutputTable d
    INNER JOIN @tDataOutputTable d2 ON d.JVID = d2.JVID AND d.dBranch <> d2.dBranch

SELECT * FROM @tDataOutputTable

我不是那样熟悉VBA和Access的复杂性，但希望这样的方法应该可以工作：

I'm not that familiar with the intricacies of VBA and Access, but hopefully something like this should work:

SQLScript = "UPDATE [DATA OUTPUT TABLE] AS d"
SQLScript = SQLScript & " INNER JOIN [DATA OUTPUT TABLE] AS d2 ON d.JVID = d2.JVID AND d.dBranch <> d2.dBranch"
SQLScript = SQLScript & " SET d.SA = d.dBranch" & Chr(34) & " Shared Appropriation" & Chr(34) & ";"
DoCmd.RunSQL SQLScript

这篇关于合并更新和选择SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！