MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Dan Ray Date:August 31 1999 1:57pm
Subject:REPLACE strategy question
View as plain text  
Hola, folks.

I recently mentioned on this list that I'm often updating tables out of text
files that have relatively few changes in them. The conclusion I came to is
that REPLACE is what I want, rather than handling each line multiple times
to determine whether I should UPDATE or INSERT. Then I found the "-r" switch
in mysqlimport, and thought my problems were solved.

Here's the trouble I'm having (which I, embarrassingly enough, didn't
consider until a client pointed it out). If a record is removed from the
text file, we want it deleted from the table, too. Doing a simple
"mysqlimport -r" obviously doesn't handle such things--it leaves records
that aren't mentioned in its data source alone, as it should.

I'm thinking of making up a Perl script that will address this, unless
somebody can suggest a way of doing it from the command-line with the
distributed binaries. I'm considering adding a timestamp field to my table,
noting the time just before I begin, doing my REPLACE work, then DELETEing
records with timestamps prior to that.

My other option, if it's a small table, is to simply delete from it, killing
all records, then import from text. I don't mind my table being "partial"
for the few seconds it would take to perform this action on a small set of
data, but if it's going to be more than, say, :30, this isn't a good way to
go. I've got some clients using MySQL in an environment where we don't have
command-line access, so I update their tables using Perl/DBI from another
machine. Obviously this is a LOT slower than using mysqlimport right on the
same box, so it doesn't take too many records for this second option to be
unworkable.

I'm sure I'm not the first person to run up against this. Anybody got any
suggestions about the best way to handle it?

Dan Ray
Account Manager
Senior Web Developer
Triangle Research, Inc.
http://www.triangleresearch.com

Thread
REPLACE strategy questionDan Ray31 Aug
  • Re: REPLACE strategy questionScott Hess31 Aug
    • Re: REPLACE strategy questionBenjamin Pflugmann2 Sep