asked on

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

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?

Gregory Miller

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.

rgb192

ASKER

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

Dan Craciun

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

Surrano

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

rgb192

ASKER

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

rgb192

ASKER

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.

rgb192

ASKER

how can I test because I think this command will update both tables and I do not want to alter both tables

ASKER CERTIFIED SOLUTION

Surrano

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

yes updated one table

this is the query I used

Thank you and thanks for other suggestions.