Code Blog

Displaying 1-1 of 1 result.
2015/05/07 Yii,MySQL

I unfortunately had made some mistakes in my Yii code, which meant I had to restore only certain values from an old copy of my database. Normally it would be easiest to just copy over the entire database from the backup, but my backup wasn't up-to-date will all the data.
Basically the old backup had correct "create_time" for old posts, but as I also had new posts still not in the backup, so I therefore wanted to just extract the values for the "postid" that existed in both databases and after that update the "create_time" from the backup. But the process would be the same for any other field in a table. 
 
1. Create a new temporary database
2. Import the backup
 
3. Then I had to run a SQL command to copy over the old correct values from the temporary database.
Here's what I run to restore the correct "create_time" values:
USE database1
UPDATE table1 t1 
JOIN tempdatabase1.table1 as t2 ON
   t1.id = t2.id
SET
   t1.create_time = t2.create_time

Note: The following name aliases were created:

database1.table1 = t1
tempdatabase1.table1 = t2


4. After restoring the damaged values, the temporary database can be removed.