Update query with right join

[Nebil]

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

I tried to insert a where statement but it only updates the rows with the same key value,Bridge Number.But also wanted to append rows even not having a common value.

[jireh]

I think there's a problem in your query.

1. First you get all data that has no Bridge Number
2. Second, you join them on the table using the field Bridge Number.

There's no way you could join them properly as sql cant identify properly what key should they pair of, in this case, you're trying to pair them with null values.

[Nebil]

@jireh
I've not included it here but i've first placed the data in the temp table.May be is it not possible that access doesn't allow this kind of update statement based on non-existing fields?