Help with update query

[Knowlton]

I am trying to update a new field in a table [tblTripDetails].[StateID] with the value from a lookup table [tlkpStates.StateID] where [tlkpStates].[State] = [tblTripDetails].[State]. I have been unable to get this to work.
Here are what I've tried:
using Dlookup;

UPDATE tblTripDetails INNER JOIN tlkpStates ON tblTripDetails.StateID = tlkpStates.StateID SET tblTripDetails.StateID = DLookUp("StateID","tlkpStates","[tlkpStates].[State] = '" & [tblTripDetails].[State] & "'");

and using a select query;

UPDATE tblTripDetails INNER JOIN tlkpStates ON tblTripDetails.StateID = tlkpStates.StateID SET tblTripDetails.StateID = (SELECT [tlkpStates].[StateID] FROM [tlkpStates] WHERE [tlkpStates].[State] = [tblTripDetails].[State]);

I am not trying to duplicate data storage. After I get the StateID field populated I'll delete the State field and then update a LocationID field the same way.

Thanks for you're help!
Knowlton

[patjones]

Hi Knowlton,

If you do the INNER JOIN correctly, then the DLookup isn't necessary because the JOIN will have already matched the records up for you. But it looks like you need to do the JOIN based on [State] rather than [StateID], because [StateID] doesn't exist in tblTripDetails yet...so the JOIN will not work. Try this:

UPDATE tblTripDetails INNER JOIN tlkpStates ON tblTripDetails.State = tlkpStates.State SET tblTripDetails.StateID = tlkpStates.StateID

Another way of writing this would be:

UPDATE tblTripDetails 
SET tblTripDetails.StateID = tlkpStates.StateID 
WHERE tblTripDetails.State = tlkpStates.State;

Pat

[Knowlton]

I copied your SQL into a new query window and viewed the results. It returned the correct number of records but all were blank. This is the same result as I was getting originally. Any ideas what may be wrong?
Thanks again!

[NeoPa]

You appreciate you're looking at an UPDATE query? What data would you expect to see there?

[Knowlton]

If you "View" the query from the query window shouldn't it show the records affected and the changes to be made so that you can be sure its what you expected before actually running the query?

[NeoPa]

Not exactly no. Viewing an UPDATE query shows you only the fields (field in your case) which are due to be changed. The values shown are the existing values for that field. If I'm not mistaken (as you describe it as a new field) I would expect the data to show as blanks when you look at it.

[Knowlton]

Thanks for your help and explanations! After you questioned what I expected to see I ran the query and it updated the table corectly.
Knowlton

[NeoPa]

I knew that because I've fallen over it myself in the past. It's a bit counter-intuitive, but it sort of makes sense (if only to MS).

[patjones]

I'm glad that everything worked out okay. When I first started working with SQL I had some problems with the concept of UPDATEing joins. Let us know if there are any other issues.