Update w/Select Subquery - Correct?

Question

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 want 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!

You are working on a test environment, right? Not learning SQL syntax on the live production server I hope... — Remus Rusanu, Jan 14 '11 at 22:15
Yes, at very worst - this would blank pricing in a database that I mostly have run over - so it just means I would have to repopulate it at some later date. I call the vendors for this anyway, so it's mostly a 'nice to have' thing. — Comrad_Durandal, Jan 14 '11 at 22:19
You should never ever use DoCmd.RunSQL. I've written an Access function to replace it called SQLRun() that I've posted in many places (google my name and the function name) that is a much better option, as it has none of the myriad problems of DoCmd.RunSQL. — David-W-Fenton, Jan 15 '11 at 00:25

score 1 · Answer 1 · edited May 23 '17 at 12:18

1

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

edited May 23 '17 at 12:18

Community

1
1

answered Jan 14 '11 at 22:14

Jason Benson

3,371
1
19
21

OP omitted parens around the subquery--it will be correctly formed with them. I still don't think it will run in Access, though. – RolandTumble Jan 18 '11 at 21:38

score 1 · Answer 2 · answered Jan 14 '11 at 22:20

1

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

answered Jan 14 '11 at 22:20

Joe Stefanelli

132,803
19
237
235

score 1 · Accepted Answer · answered Jan 18 '11 at 20:13

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.

This! See, I didn't know UPDATE FROM didn't work with Microsoft Access, this works perfectly. — Comrad_Durandal, Jan 18 '11 at 21:56

score 0 · Answer 4 · answered Jan 14 '11 at 22:21

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

Update w/Select Subquery - Correct?

4 Answers4