0

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Griehle
  • 114
  • 1
  • 10

2 Answers2

1

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.

Sorin Lascu
  • 395
  • 1
  • 4
  • 15
  • I looked into that LAST_INSERT_ID() thing, because I wondered if I had two people writing at the same time if it could be a race I don't wanna be in. But it seems that is connection specific and so far it is working. I did not even try the trigger action. Thank you for your input even though I included no Code. – Griehle Jun 16 '16 at 17:15
0

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

Community
  • 1
  • 1
Arun Prasad E S
  • 9,489
  • 8
  • 74
  • 87