Update and Inner Join Question

[snoopy92211]

This is driving me NUTS!!

I am trying to update a table based on an inner join. Do update tables have to be based on primary keys? arrrh!

the error is saying that my syntax for the update is incorrect. PLEASE HELP!


(ALL and mpslocations are tablenames, respectively)

db.Execute "Update ALL
INNER JOIN MPSLocations ON ALL.Location_Code=MPSLocations.LocationCode
& "Set ALL.LocationAddress = MPSLocations.StreetAddress, " _
& "Set ALL.LocationCity = MPSLocations.City, " _
& "Set ALL.LocationState = MPSLocations.State, " _
& "Set ALL.LocationZip = MPSLocations.Zip, " _
& "where ALL.Location_Code <> MPSLOCATIONS.LocationCode"

 

[Pat Hartman]

You only need a single "Set" and you have an extraneous coma after the last set.

db.Execute "Update ALL
INNER JOIN MPSLocations ON ALL.Location_Code=MPSLocations.LocationCode
& "Set ALL.LocationAddress = MPSLocations.StreetAddress, " _
& "ALL.LocationCity = MPSLocations.City, " _
& "ALL.LocationState = MPSLocations.State, " _
& "ALL.LocationZip = MPSLocations.Zip " _
& "where ALL.Location_Code <> MPSLOCATIONS.LocationCode"

Also your where clause conflicts with the join. The join requires a match between the two tables and the where clause is looking for differences.

 

[modest]

if you have any further SQL questions just go to www.techonthenet.com

 

[snoopy92211]

All -

I made the changes that were suggested.
The statement is x <> y, because I want to update every field where (before the update, the location codes don't match).

However, when I open my 'all' table, the location information from the master table isn't appended to the location fields in table 'all'. What do you suggest?

Thanks!
Angel

 

[modest]

Use an exists clause... I do a lot of query-checking when i create one to make sure it works... so I'm not sure this syntax is correct... but something like this:

WHERE Exists (select * from ALL,MPSLOCATIONS where ALL.Location_Code=MPSLOCATIONS.LocationCode) = False;

-modest

 

[Pat Hartman]

The statement is x <> y, because I want to update every field where (before the update, the location codes don't match).

You are JOINING on the location codes if x <> y, the record will NOT be returned. The query will not update any records - ever!

 

[modest]

What you want is a left join instead.

And then check where y is null.


for further examples, check out a similar problem I had hear:
http://www.access-programmers.co.uk/forums/showthread.php?t=82378