Mysql – thesql insert select conundrum

insertMySQLselect

I'm trying to insert select but I'm confused as to how to populate just 1 item into the destination table from the select, and populate the rest with static values.

This is all in a stored procedure… so… here's what i'm trying to do…

table:animals
id   |type| name
1    |cat  | mittens
2    |cat  | fluffy
3    |dog  | rex

table people_with_pets
persons_name | pets_name
Arnold       | rex

defined variable owner = "Joe";

so… joe loves cats and I want to insert all the names of the from the animals table into the people_with_pets table, along with "joe".

so, I'd like to select name from animals where type like 'cats';

and insert the resulting values along with "joe" into (in this case 2) new rows in people_with_pets.

I'm not sure if insert select gets me this, it seems like it might, but I'm not sure how to use it.

Best Answer

INSERT INTO people_with_pets (persons_name, pets_name) SELECT 'Joe', name FROM animals WHERE type = 'cat';