0

Is it possible to update the source table with the LAST_INSERT_ID from the target table?

INSERT INTO `target` SELECT `a`, `b` FROM `source`

The target table has an auto increment key id which I would like to store in the source table for further usage.

Would save me a lot of computing power if something like this would be possible :)

remy
  • 1,511
  • 10
  • 15

1 Answers1

1

Immediately after executing: 

INSERT INTO `target` SELECT `a`, `b` FROM `source`

Call an update on the source table as below: 

UPDATE `source` 
SET field_name = LAST_INSERT_ID() 
WHERE col_name_x = some_value_or_expression

Change column names and where conditions and then execute it.

Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
  • This works only for one entry, the UPDATE writes the first generated id to all entries, but I would need to have the correct id for each entry. – remy Apr 11 '14 at 07:54
  • 1
    Insert always generates a single id. So for each of the unique insert, you have to use that specific id, to update its related data only. `LAST_INSERT_ID()` gives the latest inserted id, in single, only. To update multiple records you should have multiple inserts and a trigger as well, if I am thinking correct. – Ravinder Reddy Apr 11 '14 at 08:04