CODEWITHSUNDEEP
mysqlsql
Vaibhavrtk
Vaibhavrtk

Reputation: 62

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

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.

Upvotes: 0

Views: 650

Answers (2)

toonice
toonice

Reputation: 2236

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)

Upvotes: 0

Sahil
Sahil

Reputation: 11

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.

Upvotes: 1

Related Questions