On Thu, 1999-09-02 14:31:30 +0200, Jesper Maartenson wrote:
> That textfile is formatted as follows:
> 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? =)
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,...);
INSERT INTO tmp2
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
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: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7