Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of my 3rd party applications that writes to my production MySQL database mydb has nulled SOME of the data in one of my fields myfield in table mytable.

I'm trying to import only the NON null records for this field from my backup mydump.sql. The ID field can be used as a unique identifier to make the comparison.

Do I have to import the backup in it's entirety to a temp location, and then build a query/command? Or can I write a query that will also look into an external file? Either way I think the query is a bit above my skill level, so any help will be appreciated.

Ideally, if myfield data in my prod table exists (i.e. not null) then we'll leave that alone, as I'm only looking to import/append myfield data if it's in the backup and not in the prod field.

share|improve this question

migrated from serverfault.com Feb 28 at 16:25

This question came from our site for professional system and network administrators.

    
"Do I have to import the backup in it's entirety to a temp location, and then build a query/command?" - yes, this will be the easiest way. –  Peter Feb 24 at 10:07

1 Answer 1

There are two solutions I can immediately think of for your current situation:

  1. Write a tool to parse the MySQL dump file, extract the records you're interested in, and insert them.
    This sounds like a lot of work, and a lot of time (especially if your dump is large).

  2. Do as you suggest in the question: Reimport the data somewhere and use a SQL query to get the fields you want.

My vote would be for (2) because you can be more precise about the selection of the rows (and the SQL will probably be faster than writing a script to parse the dump file. You'll need a machine running MySQL, with enough room to import your data, but you might be able to do that on your main server by restoring to an alternate database.

share|improve this answer
    
Yeah I thought as much. Assuming I have this temp database to reference, where might I start query wise? I'm comfortable writing short select and sort type queries, but this is a few steps above my pay grade:) –  square_eyes Feb 28 at 22:56
    
@square_eyes Without your database I can't even begin to tell you what such a query would look like. you would have to be able to uniquely identify each row in which data was lost (ideally by a primary key), extract the lost (presumably NULL) columns from your temp database, and UPDATE your main database with the missing data. The SELECT/UPDATE bit could be done with a short program in your favorite language (possibly even within MySQL itself, that's not my choice of database so I can't help there), but the identifying of rows & their missing columns nobody can do but you... –  voretaq7 Feb 28 at 23:44
    
I would have thought that some kind of loop through with an if null, then select data from secondary DB via lookup of the ID field. Is that not possible in MySQL? –  square_eyes Mar 1 at 8:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.