mysql recursive update

Question

Hi all I have two mysql tables, one containing details on cars and one containing all the possible models of the cars, as such:

cars:
  car_id
  model
  [more details]

models:
  model_id
  model_name

Now, my problem is that the model details stored in the 'cars' table is the model_name, rather than the model_id which is what I want

I'm guessing I need some kind of recursive UPDATE table to be able to update cars.model, but my brain's stopped working and can't figure out how to do it. Does anyone have any hints on how this would be done?

Thanks to anyone who can help!

Answer 1

mysql> create table cars(car_id int auto_increment primary key, model 
varchar(50), model_id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table models(model_id int auto_increment primary key, name 
varchar(50));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into models(name) VALUES ('Taurus'), ('Ka'), ('Mustang');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into cars(model) VALUES ('Ka'), ('Mustang'), ('Taurus'), 
('F150');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from cars;
+--------+---------+----------+
| car_id | model   | model_id |
+--------+---------+----------+
|      1 | Ka      |     NULL |
|      2 | Mustang |     NULL |
|      3 | Taurus  |     NULL |
|      4 | F150    |     NULL |
+--------+---------+----------+
4 rows in set (0.00 sec)

mysql> select * from models;
+----------+---------+
| model_id | name    |
+----------+---------+
|        1 | Taurus  |
|        2 | Ka      |
|        3 | Mustang |
+----------+---------+
3 rows in set (0.00 sec)

mysql> update cars,models set cars.model_id = models.model_id 
where models.name = cars.model;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from cars;
+--------+---------+----------+
| car_id | model   | model_id |
+--------+---------+----------+
|      1 | Ka      |        2 |
|      2 | Mustang |        3 |
|      3 | Taurus  |        1 |
|      4 | F150    |     NULL |
+--------+---------+----------+
4 rows in set (0.03 sec)