使用右连接更新查询 [英] update query with right join

问题描述

我想使用右外连接更新访问数据库中的表。我创建了一个临时表，从目标表复制结构并用它来存储来自excel文件的值。并使用右连接尝试更新目标表，使临时表作为源。它引发索引或主键不能包含空值。



这是代码我用来创造温度：

Hi,
I wanted to update a table in access db using right outer join.I created a temp table,copy the structure from the destination table and used it to store values from an excel file.And using right join tried to update the destination table making the temp table as a source.It raising index or primary key cannot contain a null value.

Here's the code i used for creating the temp:

"SELECT * INTO TempTable FROM " & district & " WHERE " & district & ".[Bridge Number] IS NULL"

这是更新声明：

And this is the update statement:

"UPDATE " & district & " RIGHT JOIN TempTable ON " & _
                        district & ".[Bridge Number]=TempTable.[Bridge Number] " & _
                        "SET " & district & ".[Bridge Name]=TempTable.[Bridge Name]," & _
                        district & ".[Bridge Condition]=TempTable.[Bridge Condition]," & district & ".[Km From Addis]=TempTable.[Km From Addis]," & _
                        district & ".[Bridge Length]=TempTable.[Bridge Length]," & district & ".[Bridge Type]=TempTable.[Bridge Type]," & _
                        district & ".[Construction Year]=TempTable.[Construction Year]," & _
                        district & ".[Bridge Asset Length]=TempTable.[Bridge Asset Length]," & district & ".[Average Replacement Cost]=TempTable.[Average Replacement Cost]," & _
                        district & ".[Estimated Current Replacement Cost]=TempTable.[Estimated Current Replacement Cost]," & district & ".[Acquisition Year]=TempTable.[Acquisition Year]," & _
                        district & ".[Average Age of Asset]=TempTable.[Average Age of Asset]," & district & ".[Price Index]=TempTable.[Price Index]," & _
                        district & ".[Historical Cost]=TempTable.[Historical Cost]," & district & ".[End of Design Life Year]=TempTable.[End of Design Life Year]," & _
                        district & ".[Estimated Service Life]=TempTable.[Estimated Service Life]," & district & ".[Remaining Life]=TempTable.[Remaining Life]," & _
                        district & ".[Depreciation per Year]=TempTable.[Depreciation per Year]," & _
                        district & ".[Accumulated Depreciation]=TempTable.[Accumulated Depreciation]," & _
                        district & ".[Bridges Asset Value]=TempTable.[Bridges Asset Value]"

推荐答案

您可以尝试以下示例

Hi try the following sample

UPDATE T2  SET T2.ProductGroupNAME = 'TEST'
FROM         dbo.MasProduct AS T1 RIGHT OUTER JOIN
                      dbo.MasProductGroup AS T2 ON T1.ProductGroupID = T2.ProductGroupID

这篇关于使用右连接更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！