mySQL inserting multiple records with a select

Question

Hi, I have a "dictionary table" called car_status that has an 'id' column and a 'status' column.

car_status
id     status
1      broken
2      fixed
3      working
4      fast
5      slow

I have a table called cars with the columns: id, type, status_id

cars
id    type    status_id
1     jeep     1
2     ford     3
3     acura    4

I am hoping to insert multiple records into cars and give them all the status associated with "working". What is the best/ easiest way? I know I can query and find the status_id that is "working", and then do an insert query, but is there anyway to do it with one insert query using a join or select?

I tried stuff like:

INSERT INTO cars (type, status_id) VALUES ('GM',status_id),('Toyota',status_id),('Honda',status_id) SELECT id as status_id FROM car_status WHERE status = "working"

Thanks!

Answer 1

DECLARE temp_status_id INT;
SELECT status_id 
   INTO temp_status_id 
   FROM car_status;
INSERT INTO cars (type, status_id) 
    VALUES ('GM',temp_status_id),
           ('Toyota',temp_status_id),
           ('Honda',temp_status_id);

Answer 2

This is MS SQL, but I think you can do something like this:

DECLARE @status_id int
SELECT @status_id = status_id FROM car_status WHERE status = 'working'

INSERT INTO cars (type, status_id)
SELECT 'GM', @status_id
UNION
SELECT 'Toyota', @status_id
UNION...

Is there some reason you want to do it in a single statement? Personally, I'd just write an insert statement for each row. I guess you could also create a temp table with all the new types and then join that with the status id.