Update Query

[WLC]

I'm having problems with an Update query. I have data coming in on one file that I am loading to a temp table that I want to update on another table in my database. The problem is that when I run my update query, it is wiping out everything that DOESN'T match. I have 5 fields that MUST be equal before the update can occur. I'm trying to update 3 fields in my "master" table.

Here is my SQL:

UPDATE NPPDcopy LEFT JOIN tempNPPD ON (NPPDcopy.Hour_Ending = tempNPPD.Hour_Ending) AND (NPPDcopy.TieLineName = tempNPPD.TieLineName) AND (NPPDcopy.AdjBalancingAuthority = tempNPPD.AdjBalancingAuthority) AND (NPPDcopy.BalancingAuthority = tempNPPD.BalancingAuthority) AND (NPPDcopy.OperatingDay = tempNPPD.OperatingDay) SET NPPDcopy.Import = [tempNPPD].[Import], NPPDcopy.Export = [tempNPPD].[Export], NPPDcopy.Net = [tempNPPD].[Net];

 

[namliam]

Your "LEFT JOIN" is causing your issue, change it to a normal JOIN to force only matching records to be updated.

PS when posting any code on a forum, use the [cade] and [/cade] (replace the a by o offcourse) wraps around it to make it more readable.... also to the same reason, ffs format your sql

You will agree blow is much more pleasing to understand and look at

UPDATE NPPDcopy 
LEFT JOIN tempNPPD ON (NPPDcopy.Hour_Ending = tempNPPD.Hour_Ending) 
                  AND (NPPDcopy.TieLineName = tempNPPD.TieLineName) 
                  AND (NPPDcopy.AdjBalancingAuthority = tempNPPD.AdjBalancingAuthority) 
                  AND (NPPDcopy.BalancingAuthority = tempNPPD.BalancingAuthority) 
                  AND (NPPDcopy.OperatingDay = tempNPPD.OperatingDay) 
SET NPPDcopy.Import = [tempNPPD].[Import]
  , NPPDcopy.Export = [tempNPPD].[Export]
  , NPPDcopy.Net = [tempNPPD].[Net];

 

[WLC]

That worked. Thank you.

And thank you for the advice on posting threads.