Updating query not updating properly 1

[vba317]

I am trying to write an update query with no luck. I have two tables _calc_RptData and tbl_RptData. Currently the table tbl_RptData has 0 in the column I need populated called clntid. The information I need is in the _calc_RptData table. I want to update the tbl_RptData with the info from the _calc_RptData table. When I run the query it says do you want to update 247000 records. Since I only want to update 497 records I say no.

UPDATE _calc_RptData INNER JOIN tbl_RptData ON [_calc_RptData].UCI = tbl_RptData.UCI SET tbl_RptData.clntID = [_calc_RptData.clntID]
WHERE (((tbl_RptData.clntID)=0));

 

[PHV]

Seems like you have many records in _calc_RptData for each tbl_RptData row ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

Why do you want to store the information twice? If you already have it in _calc_RptData doubling it up and putting it in tbl_RptData seems to be stepping away from normalizing...

What are you ultimately trying to do?

 

[vba317]

You are right the data I am using is not normalized. I have almost no primary keys and the data goes through 200 tables starting in SQL and migrating to access. It took the DBA six years to develop it. The model he set up is the data comes from another data source through some text files. Because the data coming from the text files isn't always correct he has set up tables that has standard information in it. So the example I gave is only the last part of the pie before the reports get done. In this case similar information comes from another four tables. When the data is being imported into SQL the data comes into temp tables, then into many permanaent tables, then the temp tables get deleted. The data than gets into access through ODBC connections. From these tables the data than gets apppended into the permanent tables in access.

In this situation I have two tables _calc_ReportData and tblRptData. The tblData table is what all the adhoc reports that I run come from. The table _calc_ReportData has some of the data that is needed for the tblData table. The _calc_ReportData table only has this month's information in it and all the information in it gets deleted after all the importing and calculations get done. The tblData table has all historical information back a couple of years.

This query that I am having an issue with is trying to update missing data in the tblData table.

Tom

 

[BigRed1212]

Well 497 x 497 is your 247,000 so, as PHV suggests, I think your join ain't in the right place.

For an UPDATE, I would expect syntax like:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

What I see you have is:

UPDATE _calc_RptData INNER JOIN tbl_RptData ON [_calc_RptData].UCI = tbl_RptData.UCI 
SET tbl_RptData.clntID = [_calc_RptData.clntID]
WHERE (((tbl_RptData.clntID)=0));

You say you need to update the clntid values in tbl_RptData with the clntid values from _calc_ReportData in cases where the value for clntid in tbl_RptData is 0. The records appear related by UCI.

I would think:

UPDATE tbl_RptData
SET tbl_RptData.clntID = _calc_RptData.clntID
WHERE (tbl_RptData.clntID=0) and (_calc_RptData].UCI = tbl_RptData.UCI)

might be closer to what you need.

I would run that on test data first as that is off the top of my head an I DO NOT HAVE A FULL UNDERSTANDING OF YOUR DATA and as such don't want to be the guy who causes you to overwrite something critical.

I would note the reports seem to be run from tblData and not tbl_RptData, so I am unsure.

 

[vba317]

Thanks this worked great!

 

[BigRed1212]

Excellent. You're welcome.