?
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
Medium Priority
?
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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