Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Please run these : 1) SHOW CREATE TABLE payments\G and 2) SHOW CREATE TABLE projects\G –  RolandoMySQLDBA Jul 19 '13 at 16:24
    
@RolandoMySQLDBA done that –  artfulrobot Jul 19 '13 at 16:46

2 Answers 2

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;
share|improve this answer
    
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
#
# 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 !!!

share|improve this answer
    
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 9:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.