MYSQL: Load Data Infile but update if same key found?

Question

I have a members table. Half the data/fields are populated through an online CMS.

But for the member's core contact detail fields, they come from a CSV exported from a desktop database.

I wanted to be able to upload this CSV and use the LOAD DATA command to update the members contact detail fields (matching on id) but without touching/erasing the other fields.

Is there a way to do this or must I instead loop through each row of the CSV and UPDATE... (if that's the case, any tips for the best way to do it?)

Load that data into another table and after that merge with one more query. — zerkms
– zerkms, Commented Apr 19, 2011 at 23:37

srkiNZ84 · Accepted Answer · 2011-04-19 23:41:41Z

1

The Load Data Infile command supports the REPLACE keyword. This might be what you're looking for. From the manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

The Load Data Infile command also has options where you can specify which columns to update, so perhaps you can upload the data, only specifying the columns which you want to update.

answered Apr 19, 2011 at 23:41

srkiNZ84

3,5685 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gio Over a year ago

Yeah, my take from those docs that even with replace set, it will not keep the other column vals, if you don't include them in the columns-to-update list, it will set them to their default vals. But I'll give it a try anyway. If it fails, I'll separate that stuff into another table.

srkiNZ84 Over a year ago

Yeah, I think it's one of those things you should test out before doing the load. I would create a table with the same schema and fill it with a few rows and do a Load Data Infile with the replace and see what happens

Collectives™ on Stack Overflow

MYSQL: Load Data Infile but update if same key found?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related