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.
Asked
Active
Viewed 57 times
2 Answers
1
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.
![](../../users/profiles/1375.webp)
womble
- 95,029
- 29
- 173
- 228
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;
![](../../users/profiles/28731.webp)
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