Update using data from another table 1

[cumap]

hello all,

I'm trying to update a StateName field in table 1 using value in abbrStateName from table 2 where that StateName field match with the fullStateName field in the table 2... Here is my code:

UPDATE Carthead SET Carthead.locState = (SELECT Locations.locState FROM Locations WHERE Locations.locName = Carthead.locState)

I got an error saying: Operation must use updateable query

Can someone tell me where did I'd gone wrong with the query on the above? Thanks!

 

[gmmastros]

What database are you using.

For Microsoft SQL Server, this should work.

UPDATE Carthead 
SET    Carthead.locState = Locations.locState
From   Carthead
       Inner Join Locations
         On Carthead.locState = Locations.locName

I'm reasonably certain that this will NOT work with Access, but something similar would.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cumap]

I'm sorry, this is actually Access db. Would you recommend a way for this?

 

[gmmastros]

For Access, I think this will work. (It's been a long time since I've done any Access stuff). You should make sure you have a good backup of the database before you try this.

Update Carthead
       Inner Join Locations
         On Carthead.locState = Locations.locName
SET    Carthead.locState = Locations.locState

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cumap]

Thanks George, you code works flawlessly as usual. Good day!!