Update w/Select Subquery - Correct?

I am nervous to pull the trigger on this query, as I have not tooled around with subqueries all that much. I wanted to make sure this beastie does what I wa开发者_StackOverflow中文版nt it to do in terms of my program.

DoCmd.RunSQL ("UPDATE Gage_Master SET Gage_Master.Est_Calibration_Cost = SELECT dbo_Gage_Master.Est_Calibration_Cost FROM dbo_Gage_Master WHERE dbo_Gage_Master.Est_Calibration_Cost <> Gage_Master.Est_Calibration_Cost WHERE Gage_Master.Gage_ID = dbo_Gage_Master.Gage_ID;")

Basically - will this puppy update the Estimated Calibration Cost in the Gage_Master table (linked to another program's MDB datastore with a table by the same name) with the value in the SQL-Server stored copy of the Gage_Master table (dbo_Gage_Master) as long as they are not equal?

Sorry if this is an already asked question - like I said, this is a pretty complex thing, and the last thing I want to do is blank out my prices in my production database because I wrote something wrong. Thanks in advance for your help!

---

I'm assuming you're trying to run this in Access. This would explaing the "Missing Operator" error you've referred to in comments--Access doesn't understand UPDATE...FROM.... Try this instead:

UPDATE dbo_Gage_Master AS dgm INNER JOIN
  Gage_Master AS gm ON dgm.Gage_ID = gm.Gage_ID
SET gm.Est_Calibration_Cost = dgm.Est_Calibration_Cost
WHERE gm.Est_Calibration_Cost <> dgm.Est_Calibration_Cost; 

Yeah, it looks like you might be updating the wrong way, because dgm comes first in the join (it just worked out that way...). That's not the case, as it's the SET clause that determines what updates actually get done.

---

Try it this way:

UPDATE gm
    SET Est_Calibration_Cost = dgm.Est_Calibration_Cost
    FROM Gage_Master gm
        INNER JOIN dbo_Gage_Master dgm
            ON gm.Gage_ID = dgm.Gage_ID
    WHERE gm.Est_Calibration_Cost <> dgm.Est_Calibration_Cost 

---

No it will not even run. It is improperly formed SQL (you have two where's, the 2nd where should be an "And" and your update is incorrect completely, you are looking for Update from)

http://msdn.microsoft.com/en-us/library/ms177523.aspx

A very simplistic update could look like the following:

UPDATE MyTable
SET MyTable.MyColumn = B.MyOtherColumn
FROM MyOtherTable as B WHERE MyTable.ID = B.ID

Here's an additional SO post that deals with this and the answerers go into much more depth that I am here.

SQL update from one Table to another based on a ID match

---

Try this. before you will run it make a DB backup or table copy!

UPDATE Gage_Master 
SET Gage_Master.Est_Calibration_Cost = dbo_Gage_Master.Est_Calibration_Cost
FROM dbo_Gage_Master 
    inner join Gage_Master 
    on (Gage_Master.Gage_ID = dbo_Gage_Master.Gage_ID)
WHERE dbo_Gage_Master.Est_Calibration_Cost <> Gage_Master.Est_Calibration_Cost;

How to make the Gage_Master table copy:

select * into Gage_Master_copy from Gage_Master