Working on an existing webapp (PHP/MySQL) I came to this point: I have 2 tables storing names and types id. The relation between them being N<---->N I have another table in between. (see picture)
I have a last table called "category" which is unpictured. What I have to do is: duplicate contracts and customers having id_category=1 to contracts and customers with same values, having id_category=3. As well as duplicating the relations between them.
So far I've managed to duplicate all contracts and customers apart, using:
INSERT INTO contract (t_name, id_category) SELECT t_name,'3' WHERE id_category=2
INSERT INTO customer (t_name, id_category) SELECT t_name,'3' WHERE id_category=2
(The "id" field being Auto-Incremented, it goes fine).
But about copying the relations, I can't figure out HOW to take the problem.
Additional info:
- Using temporary tables is fine, but I can't modify the existing structure of tables.
- Contracts may have from 0 to N customers. Customers may have from 0 to N contracts.
- I can use either strict MySQL, or with PHP.