How to duplicate related rows

Question

Tables: 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 ID from 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

score 1 · Answer 1 · answered Jul 19 '13 at 16:27

1

Are you looking for last_insert_id

INSERT INTO payments (date, amount) ;
SET @last_id_in_table1 = LAST_INSERT_ID();
 SELECT CURRENT_DATE date, amount 
 FROM payments WHERE id = @last_id_in_table1;

answered Jul 19 '13 at 16:27

Praveen Prasannan

1,526
6
24
38

ah, sorry, should have made it clear: I'm doing this for a whole bunch of records, not just one. – artfulrobot Jul 19 '13 at 16:40

score 1 · Answer 2 · answered Jul 19 '13 at 16:57

#
# Get the Latest ID in Payments
#
SELECT MAX(id) INTO @MostRecentID FROM payments;
#
# Get the amount for the Latest ID in Payments
# Insert Today's Date and that amount
#
INSERT INTO payments (date,amount)
SELECT DATE(),amount FROM payments WHERE id = @MostRecentID;
#
# Get the Latest ID in Payments
#
SET @NewID = LAST_INSERT_ID();
#
# Get the Projects From the Previous Payment
# Insert them again using the New Payment ID
#
INSERT INTO projects (payment_id,project)
SELECT @NewID,project FROM projects WHERE id = @MostRecentID;

Give it a Try !!!

Thanks for your answer. I don't understand how this could work though except in a manual loop. I'm looking for something that inserts a bunch of new payments (not just the last one) -- i.e. generating a set of new IDs -- and then the respective new Ids being used to generate the new Project records. — artfulrobot, Jul 22 '13 at 09:59

oNare · Answer 3 · 2015-08-01T03:13:44.030

Why don't you make a third table called projects_payment and you use to make a relationship between projects and payments.

projects_payment table should be like:

projects_payment:

project_id (PK) (FK to project.id)
payment_id (PK) (FK to payment.id)
modification_date (You can use for amount date)
Any other field.

I edited your tables like this:

CREATE TABLE `payments` (
  `payment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `amount` int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`payment_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `projects` (
  `project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `project` varchar(20) NOT NULL,
  PRIMARY KEY (`project_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The table projects_payment will populate it in this way:

project_id | payment_id | modification_date

 1       |      1       |  2015-06-29 12:31:23
 1       |      2       |  2015-06-29 12:31:23
 2       |      1       |  2015-06-29 12:31:23
 2       |      2       |  2015-06-29 12:31:23
 2       |      4       |  2015-06-29 12:31:23
 3       |      5       |  2015-06-29 12:31:23

I have similar question except in my scenario, I have 3 tables, not just the two. How might the relationship look like if there was a 3rd table in the mix? — HPWD, Jun 11 '19 at 14:22
This answer implies that the database design may have been wrong in the first place, and it should be fixed by introducing a third table. However, this isn't always the case. I need to duplicate entries only once in a table that was designed correctly, and changing the code to unnecessarily use 3 tables will only complicate things. — ᴍᴇʜᴏᴠ, Feb 16 '21 at 09:01

How to duplicate related rows

3 Answers3

Give it a Try !!!

Linked