Solved

I backed up a table creating an identical table and in original table mistakenly deleted columns, so how can I restore/update?

Posted on 2014-02-19
9
246 Views
Last Modified: 2014-03-24
CREATE TABLE `email_doc` (
  `id` int(11) NOT NULL auto_increment,
  `unix_timestamp` bigint(20) default NULL,
  `from_email` varchar(200) default NULL,
  `from_name` varchar(200) default NULL,
  `to_email` varchar(200) default NULL,
  `to_name` varchar(200) default NULL,
  `subject` varchar(400) default NULL,
  `body` varchar(4000) default NULL,
  `real_id` varchar(30) default NULL,
  `checked` int(11) default NULL,
  `me_description` varchar(9000) default NULL,
  `client_description` varchar(4000) default NULL,
  `sms_type` tinyint(4) default NULL,
  `client_start` datetime default NULL,
  `client_end` datetime default NULL,
  `client_total` int(11) default NULL,
  `me_start` datetime default NULL,
  `me_end` datetime default NULL,
  `me_total` int(11) default NULL,
  `real_timestamp` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unix_timestamp` (`unix_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=332 DEFAULT CHARSET=utf8;


CREATE TABLE `email_doc_bkp` (
  `id` int(11) NOT NULL auto_increment,
  `unix_timestamp` bigint(20) default NULL,
  `from_email` varchar(200) default NULL,
  `from_name` varchar(200) default NULL,
  `to_email` varchar(200) default NULL,
  `to_name` varchar(200) default NULL,
  `subject` varchar(400) default NULL,
  `body` varchar(4000) default NULL,
  `real_id` varchar(30) default NULL,
  `checked` int(11) default NULL,
  `me_description` varchar(9000) default NULL,
  `client_description` varchar(4000) default NULL,
  `sms_type` tinyint(4) default NULL,
  `client_start` datetime default NULL,
  `client_end` datetime default NULL,
  `client_total` int(11) default NULL,
  `me_start` datetime default NULL,
  `me_end` datetime default NULL,
  `me_total` int(11) default NULL,
  `real_timestamp` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unix_timestamp` (`unix_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=332 DEFAULT CHARSET=utf8;

Open in new window



exact same table structure in email_doc_bkp
which is good that I decided to create a backup table because I deleted all these columns from email_doc

  `me_description` varchar(9000) default NULL,
  `client_description` varchar(4000) default NULL,
  `sms_type` tinyint(4) default NULL,
  `client_start` datetime default NULL,
  `client_end` datetime default NULL,
  `client_total` int(11) default NULL,
  `me_start` datetime default NULL,
  `me_end` datetime default NULL,
  `me_total` int(11) default NULL,


so how can I repopulate email_doc
restore or update?
0
Comment
Question by:rgb192
9 Comments
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39872171
Did you backup the table structure and data? If so, temporarily rename the currently defective table so it is safe and out of the way. Copy and paste the SQL backup right into a SQL execution in phpMyAdmin or another admin tool you might prefer. Voila... Your table and data will be restored to the time you did the backup.

If your SQL data to too big to copy and paste, you can also run the SQL from a file for the same results.

If you did not backup the data and only the structure, you are not going to be a happy camper... Someone else might chime in but you are probably not going to be able to recover the missing column data.
0
 

Author Comment

by:rgb192
ID: 39872437
I have inserted new rows to email_doc which are after the email_doc_bkp create and insert

but I need the old rows of email_doc_bkp that have all the columns because I deleted all the fields in 9 columns in email_doc

so maybe update from email_doc_bkp to email_doc
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39872708
This is what I would do:
1. rename email_doc to email_doc_bad
2. rename email_doc_bkp to email_doc
3. from email_doc_bad, insert the rows with an id not present in email_doc. Something like:
INSERT INTO email_doc (`id`, `unix_timestamp`, `from_email`, `from_name`, `to_email`, `to_name`, `subject`, `body`, `real_id`, `checked`, `real_timestamp`)
    SELECT `id`, `unix_timestamp`, `from_email`, `from_name`, `to_email`, `to_name`, `subject`, `body`, `real_id`, `checked`, `real_timestamp`
        FROM email_doc_bad 
        WHERE `id` NOT IN (
        SELECT `id` FROM email_doc)

Open in new window

You probably can do this faster using a INSERT IGNORE, but the version with the subquery is easier to read.

HTH,
Dan
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 8

Expert Comment

by:Surrano
ID: 39872744
Assume you've already recreated the dropped windows.

Try this:

update email_doc, email_doc_bkp set
   email_doc.`me_description` = email_doc_bkp.`me_description`, 
  email_doc.`client_description` = email_doc_bkp.`client_description`,
  email_doc.`sms_type` = email_doc_bkp.`sms_type`,
  email_doc.`client_start` = email_doc_bkp.`client_start`,
  email_doc.`client_end` = email_doc_bkp.`client_end`,
  email_doc.`client_total` = email_doc_bkp.`client_total`,
  email_doc.`me_start` = email_doc_bkp.`me_start`,
  email_doc.`me_end` = email_doc_bkp.`me_end`,
  email_doc.`me_total` = email_doc_bkp.`me_total`
where email_doc.`id` = email_doc_bkp.`id`;

Open in new window

0
 

Author Comment

by:rgb192
ID: 39890880
I tried the insert and insert ignore and then started again

but these are the only columns I have deleted ( I thought I deleted more columns)

`me_description`,`me_start`,`me_end`,`me_total`,

which are a description of my hours, my start time, my end time, my total time


update email_doc, email_doc_bkp set
   email_doc.`me_description` = email_doc_bkp.`me_description`, 
  email_doc.`client_description` = email_doc_bkp.`client_description`,
  email_doc.`sms_type` = email_doc_bkp.`sms_type`,
  email_doc.`client_start` = email_doc_bkp.`client_start`,
  email_doc.`client_end` = email_doc_bkp.`client_end`,
  email_doc.`client_total` = email_doc_bkp.`client_total`,
  email_doc.`me_start` = email_doc_bkp.`me_start`,
  email_doc.`me_end` = email_doc_bkp.`me_end`,
  email_doc.`me_total` = email_doc_bkp.`me_total`
where email_doc.`id` = email_doc_bkp.`id`;

Open in new window



I only want to update email_doc  
only update one table

are you sure this query will only update one table
0
 

Author Comment

by:rgb192
ID: 39902164
I only want to update email_doc  

because the query is
update email_doc, email_doc_bkp set

where two tables are in the update clause.

I can not test because I do not want to change the data in these production tables.
0
 

Author Comment

by:rgb192
ID: 39917978
how can I test because I think this command will update both tables and I do not want to alter both tables
0
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39923776
Sorry for late answer. If you read the whole query you'll see that it reads from both tables but updates only email_doc.
If unsure, why not create sample tables from both and try the query there.
0
 

Author Closing Comment

by:rgb192
ID: 39950576
yes updated one table

this is the query I used

Thank you and thanks for other suggestions.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question