Payments with auto-increment
ID and one-to-many relationship to
Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects.
Now next month, this payment is received again and I would like to duplicate this payment and its project's records except for changing the dates.
With one table you can
INSERT INTO Payments (date, amount) SELECT CURRENT_DATE date, amount FROM Payments WHERE id IN (1,4,5,6,123) /* e.g. user-selected set of paymeents */ ...;
Obviously to re-create the child rows you need the new insert
Payments to use for the PK of the child rows.
Is there a way to do this in (MySQL) SQL?
I'm doing this for a whole set of matches, not just one. I'm looking for a more efficient way than reading it all out into -say- PHP and doing it in a loop that way.
I've been asked for the
CREATE TABLEs. They're a simple example, but here you go:
CREATE TABLE `payments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `amount` int(10) unsigned NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `projects` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `payment_id` int(10) unsigned NOT NULL, `project` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `payment_id` (`payment_id`), CONSTRAINT `y_ibfk_1` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1