MySQL: How do I use Load Data Infile and replace existing rows' fields if it is not empty field in the file

Question

I have a file with some empty fields like this: (first column being primary key- a1,b1,b2)

a1,b,,d,e
b1,c,,,,e
b2,c,c,,

I have already present in table like

a1,c,f,d,e

Now for this key a1 using replace option and lad data infile I want final output like:

a1,b,f,d,e

Here c in second column has been replaced by b, but f has not been replaced by empty string.

To make it clear: Replace field if an actual value is present in file if an empty field is present, retain the old value.

Check this site. Very handy... http://kedar.nitty-witty.com/blog/load-delimited-data-csv-excel-into-mysql-server — Rajesh, May 12 '17 at 05:49
I am working up an Answer at the moment. Can you tell us more about the naming protocol(s) for your fields? Are they `b1` through `b140`, or are they through `b70` and `c1` through `c69` and `total`, or are they something else? — toonice, May 12 '17 at 06:55

score 1 · Answer 1 · answered May 12 '17 at 06:01

1

Let consider 2 tables having 5 columns present in t1 table -columns are c1,c2,c3,c4,c5 in t2 table -columns are d1,d2,d3,d4,d5 so query will become like this:

select c1 as e1
ifnull(c2,d2) as e2,
ifnull(c3,d3) as e3,
ifnull(c4,d4) as e4,
ifnull(c5,d5) as e5
from t1
inner join t2 on c1 = d1;

hope it will helpful to you.

answered May 12 '17 at 06:01

Sahil

11
2

So I will have to create 3 different tables for this task? Load the file in one of the table and then join with already filled table and save to a new table? I have 140 column in one table so I have to write statements like this for all the columns? – Vaibhavrtk May 12 '17 at 06:06
You could improve the quality of your Answer by showing how they could incorporate this `SELECT` statement into an `UPDATE` statement that will perform the replacements that they are asking for. – toonice May 12 '17 at 08:10
above my answer will give you only output via select query.. if you want to save output of my above select query in other table, then you can simply put it under insert statement.. i.e. `INSERT INTO tbl_temp2 (f1,f2,f3,f4) select c1 as e1 ifnull(c2,d2) as e2, ifnull(c3,d3) as e3, ifnull(c4,d4) as e4, ifnull(c5,d5) as e5 from t1 inner join t2 on c1 = d1 ;` – Sahil May 12 '17 at 08:47

score 0 · Answer 2 · edited May 23 '17 at 11:54

Please try the following...

CREATE TABLE tempTblDataIn LIKE tblTable;

/* Read your data into tempTblDataIn here */

UPDATE tblTableName
JOIN tempTblDataIn ON tblTableName.fldID = tempTblDataIn.fldID
SET tblTableName.fldField1 = COALESCE( tempTblDataIn.fldField1, tblTableName.fldField1 ),
    tblTableName.fldField2 = COALESCE( tempTblDataIn.fldField2, tblTableName.fldField2 ),
    tblTableName.fldField3 = COALESCE( tempTblDataIn.fldField3, tblTableName.fldField3 ),
    tblTableName.fldField4 = COALESCE( tempTblDataIn.fldField4, tblTableName.fldField4 );

DROP TABLE tempTblDataIn;

This Answer is based on Eric's Answer at MySQL - UPDATE query based on SELECT Query.

It is also based on the assumption that the data file will contain update data only rather than update data and new records.

Yes, you will need to do a COALESCE() line for each field. You will probably have to code each line yourself. You could use a PROCEDURE if there are many fields with a repeated structure to programmatically produce the above statements, but you may find the above simpler.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Further Reading

https://dev.mysql.com/doc/refman/5.7/en/create-table-like.html (on MySQL's CREATE TABLE ... LIKE)

https://dev.mysql.com/doc/refman/5.7/en/update.html (on MySQL's UPDATE statement)

MySQL: How do I use Load Data Infile and replace existing rows' fields if it is not empty field in the file

2 Answers2