CODEWITHSUNDEEP
mysql
Griehle
Griehle

Reputation: 114

how to use primary key as custId number in second table

I have two tables, the first has an auto incrementing ID number, I want to use that as custId in the second table. I am using an insert into the first table with all the basic info, name, address etc. Then in the second table only 3 things, custId, stocknum, and location. How can I write to these two tables kinda of simultaneously since stockNum may have several values, but always attached to one custId. I hope this makes sense even without putting code in here.

Upvotes: 0

Views: 47

Answers (2)

Sorin Lascu
Sorin Lascu

Reputation: 405

You can't insert into multiple tables at the same time. You have two options. You either do two inserts

INSERT INTO table1 (col1, col2) VALUES ('value1',value2);
/* Gets the id of the new row and inserts into the other table */
INSERT INTO table2 (cust_id, stocknum, location) VALUES (LAST_INSERT_ID(), 'value3', 'value4')

Or you can use a post-insert trigger

CREATE TRIGGER table2_auto AFTER INSERT ON `table1`
FOR EACH ROW
BEGIN
  INSERT INTO table2 (cust_id, stocknum, location) VALUES (NEW.id, value3, 'value4')
END

Hope this helps.

Upvotes: 1

Arun Prasad E S
Arun Prasad E S

Reputation: 10125

After inserting in the first table, The identity field or Auto increment field generate an ID

Get this id     Refer Here(LAST_INSERT_ID() MySQL)

Then use this id to store value in the other table

Upvotes: 0

Related Questions