List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 2 1999 1:03pm
Subject:Re: Updating a table via LOAD DATA INFILE
View as plain text  
On Thu, 1999-09-02 14:31:30 +0200, Jesper Maartenson wrote:
> That textfile is formatted as follows:
> ID;Data1;Data2;Data3
> ID;Data1;Data2;Data3
> I would like to update the existing table with this textfile, but I
> can't get it to work as I want it to.
> When I use
> load data infile '/path/to/textfile' REPLACE into table thetable
> fields terminated by "\;" (Column1,Column3,Column4,Column5);
> it sure updates the right data, but sets all the undefined columns
> to the default-value for the column instead of keeping the data
> that's there.
> Am I making sense? =)

Yep! :)

As far as I know, it's not possible with MySQL to du updates of only
some of the fields with LOAD DATA.

The best I can think of now is to use two temporary tables:

  CREATE TABLE tmp1 (... same scheme like thetable ...);
  CREATE TABLE tmp2 (... same scheme like thetable ...);
  LOAD DATA INFILE '...' INTO TABLE tmp1 ... (Column1,...);
    SELECT t.ID, tmp1.Data1, tmp1.Data2, tmp1.Data3,
           t.fields4, t.field5, ...
    FROM thetable AS t, tmp1
    WHERE t.ID = tmp1.ID;
  REPLACE INTO thetable SELECT * FROM tmp2;
  DROP TABLE tmp2;
  DROP TABLE tmp1;

Ugly, but works around the limitations that INSERT/REPLACE INTO
... SELECT can't change a table if this table also is used in the
SELECT part.

It's probably better to convert your textfile into UPDATE statements
und then do normal updates.  That is, convert a line of
   UPDATE thetable SET Data1='abc', Data2='def', Data3='ghi' WHERE ID=5;
and then simply do a
   mysql dbname <textfile

There has to be taken care of quote characters and escaping, but
nonetheless I think this is a reasonable way to do the update?!

Martin Ramsch <m.ramsch@stripped> <URL: >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Updating a table via LOAD DATA INFILEJesper Maartenson2 Sep
  • Re: Updating a table via LOAD DATA INFILEsinisa2 Sep
  • Re: Updating a table via LOAD DATA INFILEMartin Ramsch2 Sep