Selectively Update Field in Table

[NumbrCrnchr]

I need to selectively update a field in my master table with new data. By
selective I mean only records that meet a particular criteria need to be
updated. My new data is in a different table, and they are related by repair
order. This is the SQL code that Access generates:

UPDATE RepairData LEFT JOIN DallasData ON RepairData.[Repair Order] =
DallasData.[Repair Order] SET RepairData.[Material Out] =
[DallasData].[Material Out]
WHERE (((RepairData.Plant)="1103"));

I know there are approx 75k records that need to be updated, but when I go
to run this query Access tells me it want to update all the records (800k).
I did this once (mistake) and Access updated the entire table, wiping out
field data on the records that don't match the criteria AND updating the
records I wanted changed. What am I missing here?

 

[John Spencer]

A guess, you are should not be using a LEFT JOIN but an INNER JOIN

UPDATE RepairData INNER JOIN DallasData
ON RepairData.[Repair Order] = DallasData.[Repair Order]
SET RepairData.[Material Out] = [DallasData].[Material Out]
WHERE RepairData.Plant="1103"

This will only update records in RepairData where there is a corresponding
record in DallasData AND only if the RepairDate.Plant = "1103"

If you want to check to see which records would be updated, you could run this
select query to see the data and the proposed new value.
SELECT RepairData.*, DallasData.[Material Out]
FROM RepairData INNER JOIN DallasData
ON RepairData.[Repair Order] = DallasData.[Repair Order]
WHERE RepairData.Plant="1103"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Clifford Bass via AccessMonster.com]

Hi,

Probably an inner join instead of a left join would do the trick.
Backup first before trying.

Clifford Bass

I need to selectively update a field in my master table with new data. By
selective I mean only records that meet a particular criteria need to be
updated. My new data is in a different table, and they are related by repair
order. This is the SQL code that Access generates:

UPDATE RepairData LEFT JOIN DallasData ON RepairData.[Repair Order] =
DallasData.[Repair Order] SET RepairData.[Material Out] =
[DallasData].[Material Out]
WHERE (((RepairData.Plant)="1103"));

I know there are approx 75k records that need to be updated, but when I go
to run this query Access tells me it want to update all the records (800k).
I did this once (mistake) and Access updated the entire table, wiping out
field data on the records that don't match the criteria AND updating the
records I wanted changed. What am I missing here?

 

[NumbrCrnchr]

Thanks for all the tips. I found my problem - I essentially had a "one to
many" relationship going on between the data field I wanted to update and the
new data. Apparently Access doesn't like this. Once I condensed the new
data down to a "one to one" relationship, all was good.

 

[Clifford Bass via AccessMonster.com]

Hi,

You are welcome. Good to hear you solved the problem.

Clifford Bass