Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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;
share|improve this answer
    
#1054 - Unknown column 'uc.id' in 'where clause' , what the ub and uc basically? –  ABTOMAT Jul 4 '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 4 '12 at 15:54
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.