Mysql backup update
I have two mysql tables, one containing the parts on cars and one containing all the possible car models, 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 are model_name and not model_id, which is what I want
I guess I need some kind of recursive UPDATE table to be able to update cars.model, but my brain stops working and can't figure out how to do this. Does anyone have any hints on how this will be done?
Thanks to everyone who can help!
0
source to share
1 answer
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)
+1
source to share