CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 723

MYSQL Insert ID from previous Insert

There are two tables that need to have new rows added.

table fish 
  fish_id
  name
  color

table tank
  tank_id
  fish_id

Say I want to insert a new fish and then assign it to a tank with it's newly created fish_id. Is there a way to do this in one query? Or would you have to insert the new fish, get the id, then do another insert?

Upvotes: 0

Views: 63

Answers (2)

Mariusz Sakowski
Mariusz Sakowski

Reputation: 3282

You can use LAST_INSERT_ID function to do this in two consecutive queries.

INSERT INTO fish (fish_id,name,color)
VALUES(NULL,'fish','red');

INSERT INTO tank (tank_id, fish_id)
VALUES(100, LAST_INSERT_ID());

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Upvotes: 3

Codesen
Codesen

Reputation: 7822

you can achieve this using trigger

CREATE TRIGGER `after_insert_fish`  
    AFTER INSERT ON `fish` FOR EACH ROW  
    BEGIN  
        INSERT INTO tank (fish_id)  
        VALUES (NEW.fish_id);  
    END

Upvotes: 1

Related Questions