I have a MySQL database with 9 tables, each table has an id with primary key set and auto increment and a row for data, one table let's call it 'X' from this database has also a primary key and one row for data and all primary keys from other tables as foreign keys, the question is can I insert into 'X' table not by id of other tables but by data row, for example some other tables has id -> 3 and data: apple , can I insert 'apple' that takes id 3, instead of id 3 that takes apple from data row ?
Add a comment
|
2 Answers
You can select and insert values in one step:
INSERT INTO X (fruit_id, ...)
VALUES ((SELECT fruit_id FROM fruits WHERE fruit = 'apple'), ...);
That will insert the fruit_id of apple.
Based on i486s comment, you can also directly insert a select result:
INSERT INTO X (fruit_id, tableb_id, tablec_id, datafiled)
SELECT fruit_id, b.tableb_id, c.tablec_id, 'some data'
FROM fruits a, tableb b, tablec c
WHERE a.fruit = 'apple',
AND b.tableb_data = 'some data from tableb'
AND c.tablec_data = 'some data from tablec';
2 Comments
i486
Why you need
VALUES((SELECT fruit_id FROM fruits...), ...) when the same can be written shorter: INSERT INTO X (fruit_id,...) SELECT fruit_id, ... FROM fruits WHERE fruit = 'apple'.
Paul Spiegel
I had your solution also in mind. But there are 9 FKs in table X, so you might end up cross joining all those 9 tables. While it would also work fine, i just didn't feel it would be a suitable solution.
No, mysql is no ms access where you could do this. You can implement an import function in mysql or even a GUI in another programming language that translates the value 'apple' into the foreign key of 3 by looking it up in the other table. That's exactly what ms access does in the background for you.
