MySQL - 查询将 1 个字段的部分内容移动到新字段

Question

我有一个名为 cartypes 的表，它目前有 2 个字段:id 和类型。我正在添加子类型，我想从名为 type 的字段中移动破折号 - 之后的内容，并将这些内容放入子类型中。处理破折号(任何额外的空格也是如此)。最好的 SQL 查询是什么？

之前:

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

之后:

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

Answer 1

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