The Old Joel on Software Forum: Part 4 (of 5)

more mysql

howdo, this place is the best.. :)

Im using mysql database and updating current data.
I have a file which I want to import in.
I want to match a value in one of the columns with a primary key, and insert the rest of the data into the matching row.

Im using:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE my_table;

and need to do something like:

>LOAD DATA LOCAL INFILE 'dealer2.txt' INTO TABLE users WHERE users.UI=input_file.UI (UI, District, CoreCode, Telephone);

But I cant find a way to load data over old data using a matching row over multiple columns, from a file.

As far as I know this will just append data.

I could possibly load this data into another table and then write an sql UPDATE query which overwrites old data from the new table.
- Cool, I figured out a way to do it in writing this.. :) however does anyone know the proper way?

Also, does anyone know how to import a fixed field format file, a text file as follows:
(10 bytes) URN (from mailing file)
(20 bytes) Surname (from mailing file)
(8 bytes) Postcode (from mailing file)

As far as I know this cant be imported without me working on the file to an extent where mysql can recognise it. e.g. tab seperated.

Thanks massively in advance..

aku beg
Thursday, April 15, 2004

I'd recommend a quick C/C++ program. If you're stumped, contact me off list and I can make the little beastie for you. This is a pretty quick bit of work.

Clay Dowling
Thursday, April 15, 2004

Load data into a temp table and then use a standard update query joining the matching rows

DJ
Thursday, April 15, 2004

>I could possibly load this data into another table and then
>write an sql UPDATE query which overwrites old data from
> the new table.

That's how I did it when I had to do something similar recently. I'm not sure if that says more about the limitations of MySQL or of my knowledge, though.

Michael Eisenberg
Thursday, April 15, 2004

The LOAD DATA statement is completely different to INSERTs and updates. It should only be used to load big chunks of data, unchanged, into a table.

Basically the LOAD statement ignores all the transaction semantics, all the referential integrity and just writes the bytes to disk. Best practise in this kind of situation is to load the data into a staging table and run updates.

However, if you're loading small chunks of data, do it in your app and run insert statements.

Koz
Thursday, April 15, 2004