UPDATE statement with a join

[thebign]

Hi everyone. I have two tables that are joined together by two fields - "Date" and "TrapID" (together they make up the primary key). I want to update a field ("DaysInField") in the 1st table based on a field ("DaysInField2") in the 2nd table, but only where a field ("Date2") in the 2nd table isn't NULL. I've tried 2 versions of code as pass-through queries in Access, and even though I don't recieve an error nothing gets updated. Any ideas?

I have a similar Access query that actually works. A png image of the design view can be seen here:

http://www.yousendit.com/transfer.php?action=download&ufid=3E33884B161681F3

Version 1:
UPDATE tData
SET tData.DaysInField = tUpdateDaysInField.DaysInField2
FROM tData
INNER JOIN tUpdateDaysInField
ON (tData.Date = tUpdateDaysInField.Date) AND (tData.TrapID = tUpdateDaysInField.TrapID)
WHERE Not (tUpdateDaysInField.Date2 Is Null)

Version 2:
UPDATE tData
SET tData.DaysInField = tUpdateDaysInField.DaysInField2
FROM tData,tUpdateDaysInField
WHERE ((tData.Date = tUpdateDaysInField.Date) AND (tData.TrapID = tUpdateDaysInField.TrapID)) AND Not (tUpdateDaysInField.Date2 Is Null)

 

[DotNetGnat]

Try this:

UPDATE A
SET A.DaysInField = B.DaysInField2
FROM tData A
INNER JOIN  tUpdateDaysInField B
ON A.Date = B.Date AND A.TrapID = B.TrapID
WHERE B.Date2 IS NOT Null

-DNG

 

[thebign]

Hey thanks for the reply. Actually, I just found out that the problem was with the connection string to the database in the pass-through query, so the SQL statement itself was fine. I'm sure your code would work too, though.