Geek Answers Handbook

How to use LOAD DATA LOCAL INFILE REPLACE

I have a script that I use to transfer data from one db to another. I have already done this using regular mysql insert and update scripts. It is just too long.

In any case, I dumped all the data that I want to update in the new db to csv, I'm just worried that if I run this script, I'm going to get duplicate entries. I do not have an "id" that matches, so I cannot perform the comparison on the REPLACE script below. I was wondering if anyone could tell me if this script looks right.

I currently have data rows in a new db, I just want to rewrite all the fields in the new db with this new data in csv if they match. The field "archived_id_number" is the only field on which I could match, but this is not the primary key.

Can someone shoot me like this:

I want to replace any data in fields with data in csv if archived_id_number on csv matches what is in the new db. If there is no match, I want to insert the data in a new row.

$sql = '
LOAD DATA LOCAL INFILE "'.$theFile.'" 
REPLACE INTO TABLE Product 
FIELDS TERMINATED BY "|" 
LINES TERMINATED BY "\\n"
(archived_id_number, sku, name, upc, account_id, shippingBox_id, unit_cost, supplier_id, description, productLength, productWidth, productHeight)
;';

Thanks for the help!!!

+4
source share
1 answer

, , . , , . , 3 (, php $sql).

$sql = '
    CREATE TABLE `_test_Product` LIKE Product; 
';

$sql = '
    INSERT `_test_Product` SELECT * FROM Product;
';

$sql = '
    LOAD DATA LOCAL INFILE "'.$theFile.'" 
    REPLACE INTO TABLE `_test_Product`
    FIELDS TERMINATED BY "|" 
    LINES TERMINATED BY "\\n"
    (
        archived_id_number, 
        sku, name, upc, account_id, shippingBox_id, unit_cost, 
        supplier_id, description, productLength, productWidth, productHeight
    );
';

( , MySQL)

+2

More articles:


All Articles