update query with right join

Question

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 15-Aug-14 18:24pm

Nebilo

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ashok kumar K · Answer 1 · 2014-08-16T03:00:00

Solution 1

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

Posted 16-Aug-14 3:00am

Ashok kumar K