Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql LOAD DATA INFILE update

Tags:

mysql

I am currently using mySQL LOAD DATA INFILE to insert a csv file into my database. This csv file is downloaded to the server on a daily basis in order to keep product data up-to-date.

What I want to know is how can I update the table with the new csv and preserve the existing data where it's not different?

Here is my current statement:

LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE products FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' IGNORE 1 LINES (aw_product_id,merchant_id,merchant_image_url,aw_deep_link,description,in_stock,merchant_name,brand_name,display_price,product_name,rrp_price,merchant_category

This works fine but it replaces the ID column with a completely new set and also reverts columns that I want to ignore, back to the default state. For example I have a column called 'Published' with a value of 0 or 1. If I use REPLACE it sets that column back to 0.

How can I use REPLACE but ignore some columns?

like image 447
Ben Gordon Avatar asked Jan 15 '23 14:01

Ben Gordon


2 Answers

The answer to How can I use REPLACE but ignore some columns? is you can't: REPLACE allways replaces a complete row, not the single field values of that row.

The answer to Can I still achieve my goal though is Yes: My recommendation would be to LOAD DATA LOCAL INFILE into another table, then use a stored procedure or query to INSERT and UPDATE (as opposed to REPLACE) your main table. If you give us a bit more information (table structure, which column matches the loaded data with the existing data) we might be able to help you further.

like image 172
Eugen Rieck Avatar answered Jan 18 '23 05:01

Eugen Rieck


If augmenting/changing the table:

First LOAD DATA into a tmp_table. Then use this to either create a new row or update an existing row:

INSERT INTO real_table
    SELECT ... FROM tmp_table
    ON DUPLICATE KEY UPDATE
        a = VALUES(a), ...

If this table is quite big, consider "chunking" those IODKUs. See my blog for chunking tips.

Note: IODKU requires a UNIQUE (possibly PRIMARY) KEY to control which row to UPDATE.

If replacing the entire table, then this is much better:

CREATE TABLE new LIKE real;
LOAD DATA ... INTO new;
RENAME TABLE real TO old, new TO real; -- atomic and fast (no downtime)
DROP TABLE old;

Replace

Do not use REPLACE; it is a DELETE plus an INSERT. If you have AUTO_INCREMENT, then those ids are thrown away ("burned"), and you could run out of ids after a few months.

like image 22
Rick James Avatar answered Jan 18 '23 04:01

Rick James