The Old Joel on Software Forum: Part 3 (of 5) - Problem with access update query

Problem with access update query

Not sure if this is the right forum to post this to, but heregoes. Probably something realllllly simple.

I am attempting to update one table from another table using two criteria, and having a hell of a time. The tables look like this(leaving out a lot of probably irrelevant values):

-- AuthoritativeDatapoint -- (30 records)
DatapointID {secondary key}
SubSystemID {primary key}
UpperLimit
LowerLimit
SetPoint

--SubSystemDatapoint-- (1 record)
DatapointID {secondary key}
SubSystemID {primary key}
UpperLimit
LowerLimit
SetPoint

I want to update AuthoritativeDatapoint LowerLimit, UpperLimit, and SetPoint where the DatapointID and SubSystemID are equal to the dataid and specificid in SubSystemDatapoint, with the values from SubSystemDatapoint where the ids are also identical.

I have checked to make sure that there are rows in both tables that match the criteria, and I have tried rephrasing the SQL query(both using SQL from scratch and the Access Query Builder). First I tried INNER JOINing the two tables together, using the IDs. No results. Then I tried using SET and FROM - access through a syntax error on the 'FROM'. Then I tried using subselects - Access threw an 'Operation Must use an Updateable Query' error. Then I tried defining the fields in 'where' criteria - also no results. Eliminating either of the ID checks returns results, but both returns nothing. My current SQL code is below.

Non-subselect version:

UPDATE AuthoritativeDatapoint, SubSystemDatapoint SET AuthoritativeDatapoint.UpperLimit = [SubSystemDatapoint].[UpperLimit], AuthoritativeDatapoint.LowerLimit = [SubSystemDatapoint].[LowerLimit], AuthoritativeDatapoint.SetPoint = [SubSystemDatapoint].[SetPoint]
WHERE (((AuthoritativeDatapoint.SubSystemID)=[SubSystemDatapoint].[SubSystemID]) AND ((AuthoritativeDatapoint.DatapointID)=[SubSystemDatapoint].[DatapointID]));

subselect version:
UPDATE AuthoritativeDatapoint SET AuthoritativeDatapoint.UpperLimit = (Select UpperLimit from SubSystemDatapoint where SubSystemDatapoint.DatapointID = AuthoritativeDatapoint.DataPointID and SubSystemDatapoint.SubSystemID = AuthoritativeDatapoint.SubSystemID ), AuthoritativeDatapoint.LowerLimit = (Select LowerLimit from SubSystemDatapoint where SubSystemDatapoint.DatapointID = AuthoritativeDatapoint.DataPointID and SubSystemDatapoint.SubSystemID = AuthoritativeDatapoint.SubSystemID ), AuthoritativeDatapoint.SetPoint = (Select SetPoint from SubSystemDatapoint where SubSystemDatapoint.DatapointID = AuthoritativeDatapoint.DataPointID and SubSystemDatapoint.SubSystemID = AuthoritativeDatapoint.SubSystemID )
WHERE (((AuthoritativeDatapoint.DatapointID)=(Select DatapointID from SubSystemDatapoint where DatapointID = AuthoritativeDatapoint.DatapointID)) AND ((AuthoritativeDatapoint.SubSystemID)=(Select SubSystemID from SubSystemDatapoint where SubSystemID = AuthoritativeDatapoint.SubSystemID)));

I have been struggling with this for two days, and I consulted a colleague more experienced with SQL/Access before posting it here. I hope it is just something simple that I am missing. I appreciate any help.

Noo-bee ;)
Tuesday, January 20, 2004

I don't know for sure, but off the top of my head, your UPDATE syntax looks flaky. You're not updating both tables, according to the one statement I read; you're updating only one. Instead of this:

UPDATE AuthoritativeDatapoint, SubSystemDatapoint SET AuthoritativeDatapoint.UpperLimit = [SubSystemDatapoint].[UpperLimit], AuthoritativeDatapoint.LowerLimit = [SubSystemDatapoint].[LowerLimit], AuthoritativeDatapoint.SetPoint = [SubSystemDatapoint].[SetPoint]
WHERE (((AuthoritativeDatapoint.SubSystemID)=[SubSystemDatapoint].[SubSystemID]) AND ((AuthoritativeDatapoint.DatapointID)=[SubSystemDatapoint].[DatapointID]));

Try this:

UPDATE AuthoritativeDatapoint
SET UpperLimit = [SubSystemDatapoint].[UpperLimit]
, LowerLimit = [SubSystemDatapoint].[LowerLimit]
, SetPoint = [SubSystemDatapoint].[SetPoint]
FROM AuthoritativeDatapoint, SubSystemDatapoint
WHERE AuthoritativeDatapoint.SubSystemID =[SubSystemDatapoint].[SubSystemID]
AND AuthoritativeDatapoint.DatapointID = SubSystemDatapoint].[DatapointID]

I haven't checked the statement for validity, but it should get you in the right direction.

Troy King
Tuesday, January 20, 2004

That's what I thought too, but unfortunately it gives me a syntax error on the FROM statement.
That is why I tried subselects - to get around the FROM error while updating a single table.

Noo-bee ;)
Tuesday, January 20, 2004

Appears to be a poor database design. If you have a one to one relationship between the table and they both describe a DataPoint you should combine then and add a seperate field telling the type of DataPoint "DataPointType" either either authoritative or subsystem. In fact you could go a step further and create a separate table instead of the field. Call the new table "tblDataPointType" and hold those types in this table. You would then have a one to many relationship. In doing this you would make your query a lot easier to write.

Maybe
Tuesday, January 20, 2004

The problem is with your keys - Access needs tables to be joined on primary keys. It can only update the "left" side when there is a one-to-one, or many-to-one relationships.

If the result could be a many-to-many, or one-to-many relationship, Access can't guarentee what values will be used for the update so it disallows the update.

You can override it by clicking the "inconsistent update" options in the query properties, but only do that if you really know what you are doing.

What ever you do I hope you have a back-up.

DJ
Tuesday, January 20, 2004

That wouldn't work in this case. Perhaps a little more information is in order.

We have three tables - datapoint, intermediatedatapoint, and subsystemdatapoint. datapoint contains a list of all the applicable datapoints in the system, and their default values. intermediate can(if values for that datapoint are present) override the values in datapoint, and subsystem can override the values in intermediate OR datapoint. What we are trying to do is create a batch process that can be run nightly to create a table that has authoritative data for each sub system - hence authoritativedatapoint. I started out by joining datapoint and subsystem(another table) to create a row for each datapoint/subsystem combination. Now I want to update those values with intermediate, and then update them with the subsystem values. How would you do this?

Noo-bee ;)
Tuesday, January 20, 2004

I'll check this out DJ. Thanks.

And this is not production yet, so my computer is riddled with backups.

Noo-bee ;)
Tuesday, January 20, 2004

Make sure SubSystemID + DataPointID is really the primary key on both tables

Then a inner join between AuthoratativeDataPoint and SubsystemDatpoint (spelling!) should work

DJ
Tuesday, January 20, 2004

Maybe I'm missing something but aren't you trying to do something like this:

UPDATE AuthoritativeDatapoint
SET SetPoint = SubSystemDatapoint.SetPoint
FROM SubSystemDatapoint
WHERE (AuthoritativeDatapoint.DataPointID =
SubSystemDatapoint.DataPointID)

I don't know about Access, but works on SQL.

(BTW, I agree w/ Maybe that there's probably a design issue here)

Lee
Tuesday, January 20, 2004

Well, they are, and an inner join between the datapoint fields produces a one to many relationship, but updates ALL of my authoritativedatapoint rows.

Any way to specify a join on a primary AND secondary key ? I'm not very good with the whole SQL thing yet. Most of the stuff I have done in the past has been easy.

Noo-bee ;)
Tuesday, January 20, 2004

Actually, scratch that, an inner join on datapoint updates the two rows with that datapoint(duh). How do I narrow this to the one row that has both keys ?

Noo-bee ;)
Tuesday, January 20, 2004

Lee, yes, that is what I am trying to do. Unfortunately I can't get access to accept a from in a set statement. Is this particular to my install ?

Noo-bee ;)
Tuesday, January 20, 2004

The error, BTW, is a missing operator error.

Noo-bee ;)
Tuesday, January 20, 2004

This worked on Access:

UPDATE AuthoritativeDatapoint a
INNER JOIN SubSystemDatapoint s
ON (
a.DataPointID = s.DataPointID
AND a.subSystemID = s.subSystemID
)
SET a.SetPoint = s.SetPoint

Sorry if I'm misunderstanding again; they ran out of coffee in the breakroom :)

Lee
Tuesday, January 20, 2004

Hey, I KNOW how that is :) I'll try it out. Thanks for the suggestion.

Noo-bee ;)
Tuesday, January 20, 2004

Well, that particular code, and all the variations of it I tried, won't even go through the modeler on my version of access. For future note, I am using version 9.0.2720 (Access 2000) on windows 98.

I'm beginning to think that this problem is endemic of the rather complex database design. Any suggestions for a somewhat better design to accomplish this three step batch run as I described above ?

The system wasn't initially designed with this batch process in mind, and I think it suffers for it. Note: We need to keep the Datapoint, intermediate, and SubSystemDatapoint tables intact, so all records must append to another table.

Noo-bee ;)
Tuesday, January 20, 2004

I didn't use their GUI thing. I went to "Create Query", Right click on the window that comes up and select "SQL View".

I might have typoed one of your names, but that idea should work (assuming I follow your problem)

I used Access 2000 as well.

Lee
Tuesday, January 20, 2004

Thanks for the help. I am changing from a multi-field key to an aggregate key, and updating my queries. That way I get a more workable 1-many relationship. At least I hope it works. ;)

Noo-bee ;)
Wednesday, January 21, 2004

Well, I fixed it by removing all keys from the final table and writing the code in VBA. It steps through and makes the changes row by row. I wanted to avoid that kind of query volume... but I've spent too much time on this already.

Thanks for all your help.

Noo-bee ;)
Wednesday, January 21, 2004

Hello No-bee... Don't know if you found your answer but recently found that I had the same question. The answer lies in the wondrous wisdom of Microsoft in not maintaining consistent syntax for their update statement even between their own products: SQL Server and Access. Here's the solution:

Update table1 inner join table2 on table1.key = table2.key set table1.value = table2.value where criteria.

Noting the date of your posting, this solution may be too late but thought you could store it away for future reference.

RWT

Robert Thomson
Wednesday, February 18, 2004