mysql - Inserting same value from csv to empty rows using php

Solution:

Importing:

LOAD DATA INFILE
INTO TABLE tmp_table
{ importing options }
(AccountName, DocumentNo, Description);
Copy code

Updating:

UPDATE tmp_table t1
JOIN tmp_table t2 USING (AccountName, DocumentNo)
SET t1.Description = t2.Description
WHERE t2.Description > '';
Copy code

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=22c292e88fa4f9edae1c767bf81f070a

There must be only one distinct non-empty value inDescription for each(AccountName, DocumentNo) pair value (i.e. more than one row with non-empty value allowed, but all these values must be the same). If not then the output is unpredictable, moreover, in some rows one non-empty value may be replaced with another non-empty value.

Of course this everything may be done with one (formally) query.

You may attach your CSV file as a table withENGINE = CSV option then copy the data into it using

INSERT INTO tmp_table (AccountName, DocumentNo, Description)
SELECT AccountName, DocumentNo, t2.Description
FROM csv_table t1
JOIN csv_table t2 USING (AccountName, DocumentNo)
WHERE t2.Description > '';
Copy code

But your CSV must not contain prefix lines which must be ignored during importing.

There must be only one non-empty value inDescription for each(AccountName, DocumentNo) pair value. If not then the amount of rows intmp_table will be multiplied. But you may edit the query and replacecsv_table t2 with according subquery which distinctsDescription value for each(AccountName, DocumentNo) pair value.

Solution:

You don't need PHP for this. Just import the CSV data (with whatever tool or language you use) into a temporary table, then you fill in the gaps with SQL too, and finally push the data into the main table. That will relieve you from coding and will make the process a lot efficient & faster.

...Code to import CSV data to temporary table

UPDATE tmp_data
SET description = document
WHERE description = '' OR description IS NULL;

INSERT INTO main_data (account, document, description) 
SELECT account, document, description FROM tmp_data;
Copy code