Solved

MySql Data Restore

Posted on 2011-09-14
2
216 Views
Last Modified: 2012-05-12
I recently had to restore a backup and to say the least it went bad.  I have missing records.  I now have two tables one is a good backup of the originally lost data (loanFindingReplace) and a live table that has some, but not all of the original findings.  I can not just replace the current live table (loanFinding) since I have had number entries since the restore.  How is the easiest/quickest query to determine which records don't exist in the loanFinding table that do exist in the loanFindingReplace table and insert those into loanFinding?  Both tables have the same fields with the same names.  Fields
Thank you.
0
Comment
Question by:rcowen00
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
Hi.

Sorry to hear about the failed restore. That can be a pain in the tail. To answer your question, I would start with a NOT EXISTS clause.

It works in general as:

SELECT {column list}
FROM TableA
WHERE NOT EXISTS (
   -- don't need to actually return data, so literal 1 is fine.
   SELECT 1 
   FROM TableB
   WHERE TableB.UniqueColumn = TableA.UniqueColumn
)
;

Open in new window


In your case, that may look like:
SELECT loanFindingKey
FROM loanFindingReplace r
WHERE NOT EXISTS (
   SELECT 1
   FROM loanFinding l
   WHERE l.loanFindingKey = r.loanFindingKey
)
;

Open in new window


Note: you may need to look at some other data that identifies a match if it is possible that you have duplicate auto_number values for non-matching rows due to the data lost. If not, then should be as simple as above.

Hopefully that helps!

Kevin
0
 

Author Closing Comment

by:rcowen00
Comment Utility
Thank you.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now