How to insert and update data into 3 tables at once

customers phonenumbers customers_has_phonenumbers

customers -> detailed
customers.customer_id int(11) primary auto_increment
customers.customer_name varchar(255)

phonenumbers -> detailed
phonenumbers.phonenumber_id int(11) primary auto_increment
开发者_C百科phonenumbers.phonenumber_type int(11)
phonenumbers.phonenumber_name varchar(255)
phonenumbers.phonenumber varchar(15)

customers_has_phonenumbers -> detailed
customers_has_phonenumbers.customer_id int(11) -> customers_customer_id
customers_has_phonenumbers.phonenumber_id int(11) -> phonenumbers_phonenumber_id

how to insert and update a phonenumber

hope you guys can help

---

Insert a new phone number

insert into phonenumbers (phonenumber_type,phonenumber_name,phonenumber) values (1,'example name','example number');

insert into customers_has_phonenumbers (customers_customer_id,phonenumbers_phonenumber_id) values ((select customer_id from customers where customer_name = 'example customer name'),(select phonenumber_id from phonenumbers where phonenumber = 'example number');

Update a phone number

insert into phonenumbers (phonenumber_type,phonenumber_name,phonenumber) values (1,'example name','new phone number');

update customers_has_phonenumbers set phonenumbers_phonenumber_id = (select phonenumber_id from phonenumbers where phonenumber = 'new phone number') where phonenumbers_phonenumber_id = (select phonenumber_id from phonenumbers where phonenumber = 'old phone number') and customers_customer_id = (select customer_id from customers where customer_name = 'example customer name');

Not sure how you would handle having customers with the same customer name!

It is probably worth having a unique key on the phonenumbers.phonenumber column too if that is reasonable. Otherwise whatever uniquely identifies the phonenumbers row (apart from the phonenumbers.phonenumber_id) should be used in the update sub-select to retrieve the phonenumbers.phonenumber_id value. Same goes for the customer sub-select in the same update statement.

Furthermore, I am not sure whether you already have a customers row when inserting a new phone number? If not then you'll have to insert the new customers row before the insert into the customers_has_phonenumbers table.

Hope this helps.

---

See Multiple-table syntax on this page.