0

I yesterday made a mystake and deleted some rows in a mysql table (luck that I've set LIMIT 2000).

Now I want to use a backup to insert only the deleted rows again. I got:

tb_production:

id (primary key)   col1     col2
--------------------------------
   1               data     data
   2               data     data
   6               data     data
   9               data     data

As you see, there are the rows missing. Now I want to insert the missing rows from the backup table into the (above) production table:

tb_backup:

   id (primary key)   col1     col2
    --------------------------------
       1               data     data
       2               data     data
       3               data     data
       4               data     data
       5               data     data
       6               data     data
       7               data     data
       8               data     data
       9               data     data

I've found a good answer here on stackoverflow (How can I merge two MySQL tables?), given answer:

INSERT IGNORE INTO table_1 SELECT * FROM table_2;

I don't want to do another mistake again, so I ask you:

How to merge the rows from the backup table into the production table, without overwriting existing rows in the production table and with a LIMIT 2000 again?

Community
  • 1
  • 1
lickmycode
  • 2,069
  • 2
  • 19
  • 20
  • I am not clear with your question....did you have your backup data? – Vamshi .goli Jan 13 '15 at 10:47
  • @Vamshi.goli yes I got a 2 weeks old database backup, better than nothing I guess. I want to use the rows in the table from there to insert into the running database. – lickmycode Jan 13 '15 at 10:55
  • Now you want to insert those database into the existing database right!!!!!!!!! – Vamshi .goli Jan 13 '15 at 10:57
  • @Vamshi.goli No, I will dump only the table from the backup, insert it into the production database (renamed the table before), then copying the missing rows from the backup table into the table used in the production database. – lickmycode Jan 13 '15 at 11:05
  • what ever the id value in production table and id value in backup table are same?...this i want to know.....you have asked that you don't want to overwrite right!!!!!!! – Vamshi .goli Jan 13 '15 at 11:10
  • @Vamshi.goli Yes, the IDs are equal in both tables. However my plan is to only insert missing IDs and that's why overwriting of existing IDs in the production table isn't needed at all. – lickmycode Jan 13 '15 at 11:33
  • there is no problem brother you can directly dump there is no threat of overwriting – Vamshi .goli Jan 13 '15 at 11:36

1 Answers1

0

Please Try following query

INSERT INTO tb_production(id,col1,col2)
    SELECT bkp.id,bkp.col1,bkp.col2 FROM tb_backup bkp LEFT JOIN tb_production prd
    ON bkp.id=prd.id WHERE prd.id IS NULL ORDER BY bkp.id LIMIT 2000;
Yograj Sudewad
  • 343
  • 2
  • 9