Inner Join Problem

[JimUK]

I am probably missing something really stupid here but been pulling my hair out today.
I have an Equip Table that has a field PCN (Product Control Number). These PCN's are all being updated (someones great idea) and I have created a NEW_PCN field in tblEquip. I received an Excel spreadsheet that has the Old_PCN and New_PCN in it and imported to a table called New_Old_PCN.
I figured I could just make an update query, the relationship set to when Equip.PCN and New_Old_PCN.OLD_PCN are the same update the new PCN to the Equip table. Looks like everything in the query works fine no errors and says its going to change XX records and I see no change whatsoever??? The actual SQL follows:

UPDATE Equip INNER JOIN New_Old_PCN ON Equip.PCN=New_Old_PCN.OLD_PCN
SET New_Old_PCN.NEW_PCN=Equip.NEW_PCN


Any help greatly appreciated


Jim

 

[mikevh]

Jim --

The table you're updating is EQUIP, right?
Then shouldn't the SET statement in your SQL be
SETTING a field in the EQUIP table? It looks to
me like you've got it backwards, like you're
SETTING a field in the NEW_OLD_PCN equal to a
field in the EQUIP table rather than the other
way round. I believe it should be

SET Equip.NEW_PCN = New_Old_PCN.NEW_PCN

not

SET New_Old_PCN.NEW_PCN=Equip.NEW_PCN

but these field and table names are enough to get
anyone confused. I think I'd take a look at your
New_Old_PCN table and see if your query has changed
that.

 

[JimUK]

Well that worked just fine. Was having one of those days. Thanks for setting me straight

Jim