Inner Join problem

Swillsy

Registered User.
Local time
Today, 09:51
Joined
Jun 10, 2008
Messages
68
Hi, Im trying to make a query to update a table i just added an extra column too however I'm getting an error:

this query must be updatable

I think it has something to do with the join in the query. How would i go abotu creating a outer join?

Heres my current query:

UPDATE Applicants INNER JOIN tblStudents_Current ON Applicants.Person_Code=tblStudents_Current.Person_Code
SET Applicants.Forename = tblStudents_Current.Forename
WHERE ((Applicants.Person_Code)=tblStudents_Current.Person_Code);

Help would be muchos appreciated:)
 
Hi, Im trying to make a query to update a table i just added an extra column too however I'm getting an error:

this query must be updatable

I think it has something to do with the join in the query. How would i go abotu creating a outer join?

Heres my current query:

UPDATE Applicants INNER JOIN tblStudents_Current ON Applicants.Person_Code = tblStudents_Current.Person_Code
SET Applicants.Forename = tblStudents_Current.Forename
WHERE Applicants.Person_Code = tblStudents_Current.Person_Code;


Help would be muchos appreciated:)
  • Is the Where Statement even needed? The Join has the same requirements.
  • I also suspect that your design may not be properly normalized
 
Hi MSAccessRookie, thanks for your swift reply.
I wasn't sure whether its needed either tbh have tried it with and without the WHERE statement and stil lget the error which is why I'm sure its a join issue.

Basically I am changing a table by adding extra columns and need to update the table, which already has lots of records in it, as a one off.
 
Hi MSAccessRookie, thanks for your swift reply.
I wasn't sure whether its needed either tbh have tried it with and without the WHERE statement and stil lget the error which is why I'm sure its a join issue.

Basically I am changing a table by adding extra columns and need to update the table, which already has lots of records in it, as a one off.

I think that I understand, but if you are going to keep both tables, then you will have duplicated data in each. This is not normalized, and will cause maintenance issues in the future.

A better way might be to create a new table (tblPeople?) that contains the person information, and use the PK for that table as a FK in each of the two tables you are working with.
 
Since you removed the WHERE clause, it seems surprising to me that you'd still get this error. Are you using a multicolumn primary key?

Because, if you are joning on part of the key, and updating another part of the key, this would likely cause problems.
 
I'm not keeping both tables because I can only update data on one of the tables. So im moving the needed info from the tblStudents_Current into my table Applicants.

I have tested it a bit and Its not the WHERE clause of the SET values that cause the problem. It seems to be the way the join is setup
 
Just a quick update -
the code from my original code should work to update the table.
however having talked to the administrator it became clear that there was an issue with permissions allowing me to update from the table.

As a workaround I created a table to select into records then updated my table from that table.
 

Users who are viewing this thread

Back
Top Bottom