Mysql LOAD DATA INFILE update

Question

I am currently using mySQL LOAD DATA INFILE to insert a csv file into my database. This CSV file is uploaded daily to the server to update product data.

I want to know how I can update a table with the new csv and save existing data where it does not differ?

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 replaces the identifier column with a completely new set, and also returns the columns that I want to ignore back to the default state. For example, I have a Published column with a value of 0 or 1. If I use REPLACE, it returns that column to 0.

How can I use REPLACE but ignore some columns?

Answer 1

Answer to the question: How can I use REPLACE, but ignore some columns? you cannot : REPLACE allways replaces the complete row, not the single field values ​​of that row.

Answer: Can I achieve my goal, but yes . My recommendation would be LOAD DATA LOCAL INFILE in another table, then use a stored procedure or query for the INSERT and UPDATE (as opposed to REPLACE ) of your main table. If you give us a little more information (table structure, which column corresponds to the loaded data with existing data), we could help you in the future.

Answer 2

When enlarging / changing the table:

First LOAD DATA in 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 large, consider splitting these IODKUs. See my blog for discussion.

Note. IODKU requires a UNIQUE (possibly PRIMARY ) KEY to control the UPDATE string.

Replacing the whole table 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 ; this is DELETE plus a INSERT . If you have AUTO_INCREMENT , then these identifiers are thrown out (“burned”), and after a few months you may run out of identification.