MySQL - Query to move part of 1 field's contents to a new field

Question

I have a table called cartypes which currently has 2 fields: id & type. I am adding subtype and I want to move the contents after the dash - from the field called type and put those contents into subtype. Disposing of the dash (any extra whitespace too for that matter). What's the best SQL query for this?

before:

id        type                                      subtype (currently empty)
------------------------------------------------------------------------------
1     legendary muscle car  - steel          
2     crappy sedan - plasticky interior
3     legendary sports car  - aluminum bodywork
4     compact car

after:

id        type                     subtype
---------------------------------------------------------------
1     legendary muscle car        steel          
2     crappy sedan                plasticky interior
3     legendary sports car        aluminum bodywork
4     compact car

Answer 1

That's an update statement involving two SUBSTR() (one for each new column) and a string LOCATE() on the original column. I don't really use MySQL much, but this trick works everywhere, just the syntax of the string finding routines changes.

Answer 2

UPDATE table
 SET subtype = TRIM(SUBSTRING(type, LOCATE('-', type) + 1)),
     type = TRIM(SUBSTRING(type, 1, LOCATE('-', type) - 1))
 WHERE type LIKE '%-%';