How to re-key a hierarchy in a table?

Question

I have a two tables

cars: contains hierarchy data about cars

+-----+-------------+-----------+
| id  | description | parent_id |
+-----+-------------+-----------+
|   1 | All cars    |         1 |
|  30 | Toyota      |         1 |
|  34 | Yaris       |        30 |
|  65 | Yaris       |        30 |
|  87 | Avensis     |        30 |
|  45 | Avensis     |        30 |
| 143 | Skoda       |         1 |
| 199 | Octavia     |       143 |
|  12 | Yeti        |       143 |
+-----+-------------+-----------+

car_mapping: contains mapping data where duplicate cars (with different ids) are mapped to one id.

+--------+----------+--------+
| car_id | car_name | map_id |
+--------+----------+--------+
|     34 | Yaris    |      1 |
|     65 | Yaris    |      1 |
|     87 | Avensis  |      2 |
|     45 | Avensis  |      2 |
|    199 | Octavia  |      3 |
|     12 | Yeti     |      4 |
|     30 | Toyota   |      5 |
|    143 | Skoda    |      6 |
|      1 | All cars |      0 |
+--------+----------+--------+

Now, the idea is to create a third table, cars_new, based on cars and car_mapping which removes duplicates and re-keys the hierarchy in the cars table based on the map_id field in the car_mapping table. Here is the resulting cars_new:

+--------+----------+---------------+
| map_id | car_name | parent_map_id |
+--------+----------+---------------+
|      0 | All      |             0 |
|      1 | Yaris    |             5 |
|      2 | Avensis  |             5 |
|      3 | Octavia  |             6 |
|      4 | Yeti     |             6 |
|      5 | Toyota   |             0 |
|      6 | Skoda    |             0 |
+--------+----------+---------------+

Here is the SQL Fiddle for this question. Any ideas how to re-key this hiearchy?

Answer 1

select distinct cm.map_id, cm.car_name, cm2.map_id parent_map_id
from cars c, car_mapping cm, car_mapping cm2
where c.id = cm.car_id
and c.parent_id = cm2.car_id(+)
order by cm.map_id;

PS: in your car_mapping table, you need one extra line (first one below) to get exactly the result you want:

+--------+----------+--------+
| car_id | car_name | map_id |
+--------+----------+--------+
|     1  | All      |      0 |
|     34 | Yaris    |      1 |
|     65 | Yaris    |      1 |
Etc..