0

I have 'locations' database and two tables. My first table was 'uszips' which looks like:

--id--zipCode--stateCode--latitude--longitude--
   1    35004         AL  33.584132 -86.515570
   2    35005         AL  33.588437 -86.959727

Now, I have a second table called 'usstates' and I have 2-letter state codes already there:

--id--stateCode--
   1         AK
   2         AL

I was not able to write a query to modify 'uszips' table as 'stateCode' column would be 'stateId' to be foreign keys of the 'usstates'. For example:

--id--zipCode--stateId--latitude--longitude--
   1    35004        2  33.584132 -86.515570
   2    35005        2  33.588437 -86.959727

My best try is:

update uszips set uszips.stateCode=usstates.id
from uszips
join usstates on (uszips.stateCode=usstates.state)

But I recieve the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from uszips join usstates on (uszips.stateCode=usstates.state)' at line 2
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Litestone
  • 529
  • 7
  • 22
  • 1
    Well, you can’t just invent your own syntax, you have to follow the one that is described in the manual … https://dev.mysql.com/doc/refman/5.5/en/update.html – CBroe May 02 '15 at 14:16
  • possible duplicate of [MySQL - UPDATE query based on SELECT Query](http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) – Dave May 02 '15 at 14:17
  • 1
    I'm not sure why people are downvoting this question; it seems a reasonable attempt to solve the problem, but as CBroe points out, this isn't valid syntax. The question I marked as a possible duplicate shows how to do this sort of thing in its accepted answer. – Dave May 02 '15 at 14:19

1 Answers1

1

The set clause should come after the join clause:

UPDATE uszips
JOIN   usstates ON uszips.stateCode = usstates.state
SET    uszips.stateCode = usstates.id
Mureinik
  • 297,002
  • 52
  • 306
  • 350