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
245 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:Technodweeb
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL simple update statement 3 54
MS SQL Server - Looking to filter rows based on column value 3 37
when to use sequences in mysql 4 29
AWS EC2 & RDS Instance 5 35
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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