Updating a table with contents of another table

[benjam]

I have a couple tables with the following (trimmed) schemas

 

reps

-----------

id (PK)

name

state (contains 2 char state abbr)

 

states

----------

id (PK)

name

abbr (contains 2 char state abbr)

 

 

What I'd like to do, is to read the reps.state value, and replace it with the state.id value...

something like this:

UPDATE reps
SET reps.state = (
    SELECT states.id
    FROM states
    WHERE states.abbr = reps.state
) ;

I know that query won't work, but that should give you the idea of what I'm looking for.

 

Any ideas?  I could loop through with PHP, but I was looking for a more elegant MySQL only method.

[benjam]

ummm...  nm, apparently that query did work   

 

I went in to look at some things, and noticed the states listed were ids, and not abbrs anymore.

 

neat.