select statement in an insert statement + getting primary key for insert !!
468183Jan 25 2007 — edited Mar 8 2007Hi peeps.
I want to insert a record into a table but I need to find out a genre code to be able to do an insert.
I have a music table that holds a list of music and a genre table that holds a list of musical genres. The music table has a column "genre_id", a foreign key from the genre table.
When a user submits a form containing music info like artist, release date etc to add an item to the music table, I was going to pull up the genre id from the genre table with a simple select statement then use that genre id in a second statement to insert the item into the table.
But I wondered if there was a way to use the select statement inside the insert. e.g insert into music values('Madonna', 'CD', select genre_id from genre where etc).
It looks stupid to me but if it is possible to do something like that then great, otherwise I'll stick to using 2 seperate statements.
My second question relates to using triggers/sequences for primary keys. I have it working great but what I need to do is a second insert into another table. I want to use the same primary key code on another table to insert some different info which relates to the info inserted on the first table.
I can only think of doing an initial insert, then doing a query using the info in the insert, then using the primary key from that resultset to do a second insert. This seems cumbersome and takes 3 statements to do a full insert of the data. Is there a better way of doing this ???
Thanks
Kevin