MySQL – Duplicating Rows with Incrementing ID

duplicationMySQLrow

I have the strenuous task of merging multiple Magento databases together and I am importing hundreds of blocks into the main database but subsequently have to go through every one and change the store ID to associate each imported block with the relevant store in the multi-store environment.

Hold on before you move me over to Magento SE

I'm just after a single query I can run in the database to duplicate a row (x) amount of times and auto-increment the ID column following on from the last in sequence.

For example, I have an existing table like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       49 ║        1  ║
║       50 ║        1  ║
║       51 ║        1  ║
║       52 ║        1  ║
║       53 ║        1  ║
║       54 ║        1  ║
║       55 ║        1  ║
║       56 ║        1  ║
║       57 ║        1  ║
║       58 ║        1  ║
║       59 ║        1  ║
╚══════════╩═══════════╝
...

And I need to add new rows like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       60 ║        2  ║
║       61 ║        2  ║
║       62 ║        2  ║
╚══════════╩═══════════╝
...

So that the end result is like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       58 ║        1  ║
║       59 ║        1  ║
║       60 ║        2  ║
║       61 ║        2  ║
║       62 ║        2  ║
║       63 ║        2  ║
║       64 ║        2  ║
║       65 ║        2  ║
║       66 ║        2  ║
║       67 ║        2  ║
║       68 ║        2  ║
╚══════════╩═══════════╝
...

I am hoping there is a single query that I can use which can add each row with a specified store_id of 2 and allow the id to auto-increment?

I have unfortunately not managed to find an example of this online but I suspect it should be possible/relatively straight forward?

Best Answer

If the id column has the AUTO_INCREMENT property, then it's pretty straight-forward. Don't include it in the insert list:

INSERT INTO tableX (store_id, column3, column4, ...)
SELECT 2, column3, column4, ...
FROM tableX
WHERE store_id = 1 ;

This will insert as many rows as there are with id = 1.

If you want to do this for multiple store_id values (not only for 2), i.e. for example for 10 store ids, inserting 10N rows where N is the number of rows with id = 1, you can extend to:

INSERT INTO tableX (store_id, column3, column4, ...)
SELECT s.store_id, t.column3, t.column4, ...
FROM tableX AS t
  CROSS JOIN 
    ( SELECT 2 AS store_id UNION ALL
      SELECT 3 UNION ALL
      ---
      SELECT 11
    ) AS s
 WHERE t.store_id = 1 ;
Related Question