A couple of questions:
First off, CJ said I have the statement the wrong way around. We are trying to update tbl537 with values from tblTemp. I'm definitely confused as the above query does just that when executed.
xps35's code works as well.
Secondly, we have moved tables to MS Server. If I execute the following code working with tblTemp (local) and dbo_tbl537 (on the server) we get this error:
"You tried to assign the null value to a variable that is not a variant data type".
dbo_tbl537 was created by exporting local tbl537.
I cannot find any differences in the structue between tbl537 and dbo_tbl537.
Code:
UPDATE dbo_tbl537 LEFT JOIN tblTemp ON dbo_tbl537.LabID = tblTemp.[LabID] SET dbo_tbl537.LabID = tblTemp.[LabID], dbo_tbl537.[Date] = tblTemp.Date, dbo_tbl537.PFOS = tblTemp.PFOS;
or
Code:
UPDATE dbo_tbl537 INNER JOIN tblTemp ON dbo_tbl537.LabID = tblTemp.LabID SET dbo_tbl537.[Date] = tblTemp.Date, dbo_tbl537.PFOS = tblTemp.PFOS;
Both of the code snippets above give the null variant error when trying to execute the code on a table located on the server.
So...
1) If I do have the code backwards, I am confused as to how to edit the snippet so that it would upsert values from tblTemp to dbo_tbl537.
2) How do I get rid of the null variant error I get when executing code that points to a table that is on our server.
Final note: I get the same errors executing xps35's code on local tables as I do when I execute my original code LOCALLY. When I use either snippet with a table stored on the server - null variant error.
I think it's time to go have a cold craft beer