Can a result from one query be passed to another?

[peterv12]

I'm a newbie, so please be kind. I have two tables, authors and books. The primary table is authors, and it's primary key is authorid. The foreign key for the books table is also authorid. What I'd like to do is create a query selects the author's last name, and returns the authorid from the authors table. I then want to pass that authorid to a second query to add a book to the books table, using the authorid from the first query. Does anyone know if this is possible? I've never updated a table that uses foreign keys, and I'm unsure how to do it properly. I would appreciate all help and comments.
Peter V.

 

[vacunita]

While you can do it in a query, this is usually done from the front end. You get the Author ID prior to issuing the insert query and pass it directly to the insert query for the books table.


Basically you would already have the Author selected for the book, before you attempt to add a book for him.



But if you wanbt to attempt it it would be done like:

INSERT INTO tablename field1, field2, field3,field4, VALUES("value field1", "value field 2", [blue](SELECT fieldname FROM tablename WHERE fieldname2="string")[/blue], "value field4");

You must be sure the SELECT query will only ever return on row, one value. Otherwise the Insert will fail.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[peterv12]

Thanks for the quick response. The key I'm using for the authorid is an auto-increment format, so I'd have to look it up every time I want to add a book, that's why I thought this might be simpler.

 

[guelphdad]

Use
INSERT INTO tablename field1, field2, field3,field4, VALUES("value field1", "value field 2", (SELECT fieldname FROM tablename WHERE fieldname2="string"), "value field4");

as suggested above, don't worry about getting the author id ahead of time.

 

[vacunita]

The key I'm using for the authorid is an auto-increment format, so I'd have to look it up every time I want to add a book

Technically speaking, you would select an Author, and display the relevant data for him form the author table. At this point you have his ID. You then get the option to add a book for him.


But As I said you can do it the query way, but you need to make sure that the Author's name by which you search is performed is unique.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[DonQuichote]

An alternative way is to use variables:

-- If there is a uniqueness constraint on the authorname
-- field, the following INSERT skips silently if the name
-- already existed:
INSERT IGNORE INTO authors(authorname) VALUES('Terry Pratchett');
-- Get the ID:
SELECT authorid INTO @Id FROM authors WHERE authorname='Terry Pratchett';
INSERT INTO books(booktitle,authorid)VALUES('Going Postal', @Id);

It can even be done easier:

INSERT INTO authors(authorname)
       VALUES('Terry Pratchett')
       ON DUPLICATE KEY UPDATE authorid = LAST_INSERT_ID(authorid);
INSERT INTO books(booktitle,authorid)
       VALUES('Going Postal', LAST_INSERT_ID());

The LAST_INSERT_ID() function can be used to both query and set the last inserted ID. This is what is done in the first INSERT statement: If the row cannot be inserted because of a uniqueness constraint, the DUPLICATE KEY clause makes sure the LAST_INSERT_ID is set to the existing authorid. Read the fine manual on the INSERT statement. It's all there!

If you have more than one related record (for instance, a publisherid from a publishers table as well), you will have to use variables or pass the IDs from your application.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)