MySQL Unite Two Tables

Question

Situation: after crash (or hack, I'm not still sure) some in table vB_users disappeared. I have march backup of that table, but since that time there were new records. Is there a fast way to add all the rows from crashed table to the backup table and rewrite the existing rows? I mean, unite them, but the crashed version shoul have the priority over old version.

score 1 · Answer 1 · answered Jul 04 '12 at 15:13

Rename the crashed table, restore the backup (from MARCH? ouch), rename that one, create a new table with the old name, then do something like:

INSERT INTO vB_users SELECT * FROM crashed_table UNION SELECT * FROM restored_table;

This might not work quite right in the event of records that have the same PK but different values; you can probably do some sneaky things to fix that up.

score 1 · Accepted Answer · answered Jul 04 '12 at 15:14

1

Assuming that your crashed table is in a DB called current, your restored backup is in a DB called backup, and there's a primary key called id:

create table current.tempUsers as select * from backup.vB_users ub where not exists (select 1 from current.vB_Users uc where uc.id = ub.id);

insert into current.vB_users select * from current.tempUsers; 

drop table current.tempUsers;

answered Jul 04 '12 at 15:14

Mike Scott

7,903
29
26

#1054 - Unknown column 'uc.id' in 'where clause' , what the ub and uc basically? – ABTOMAT Jul 04 '12 at 15:45
ub and uc are just aliases for backup.vB_users and current.vB_users to save typing. Is there actually an "id" column? – Mike Scott Jul 04 '12 at 15:54

MySQL Unite Two Tables

2 Answers2