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?
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With