Click here to Skip to main content
15,313,985 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

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


And this is the update statement:

VB
"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]"
Posted

1 solution

Hi try the following sample
SQL
UPDATE T2  SET T2.ProductGroupNAME = 'TEST'
FROM         dbo.MasProduct AS T1 RIGHT OUTER JOIN
                      dbo.MasProductGroup AS T2 ON T1.ProductGroupID = T2.ProductGroupID
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900