Mysql – How to duplicate related rows

insertMySQL

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

Best Answer

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;
Related Question