CODEWITHSUNDEEP
mysqldatabase
JONNI CAREYN
JONNI CAREYN

Reputation: 97

MySql Database inserting data

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 ?

Upvotes: 1

Views: 57

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31812

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';

Upvotes: 1

Shadow
Shadow

Reputation: 34243

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.

Upvotes: 0

Related Questions