user123456789 user123456789 - 6 months ago 16
MySQL Question

Update Database table from Excel file

I have an excel file which contains many columns. I have two database tables -

job_new
and
job_dockets
.
I'm trying to create an sql query that pulls two columns from the excel file (JobNo and CustManRef), and updates the field
3rdPartyRef
from the
job_new
table but only where the field
DocketNo
from the
job_dockets
is equal to the
JobNo
column from the excel file.

CREATE TEMPORARY TABLE your_temp_table LIKE job_new;

LOAD DATA INFILE 'C:/Users/cdaly/Desktop/test.csv'
INTO TABLE your_temp_table
FIELDS TERMINATED BY ','
(JobNo,CustManRef);

UPDATE job_new j
LEFT JOIN job_dockets d on d.JobID = j.ID AND j.CompanyID = 195
INNER JOIN your_temp_table on your_temp_table.JobNo = d.DocketNo
SET j.3rdPartRef = your_temp_table.CustManRef;

DROP TEMPORARY TABLE your_temp_table;


The query just keeps saying 0 rows affected, so its not updating the job_new table.

So what I am trying to do is set the
3rdPartyRef
from
job_new
to the
CustManRef
from the excel where
docketno
from
job_dockets
is equal to the
JobNo
column in the excel file

Example of
job_docket
:

enter image description here

Example of
job_new
:

enter image description here

Excel file:

enter image description here

Ok I hope these examples make it clearer.
So the
JobNo
from the excel file is equal to the
DocketNo
from
job_dockets
. So when the
JobNo
is equal to the
DocketNo
I then need to update the
3rdPartyRef
from
job_new
to the
CustManRef
from the excel.

Answer

Based on the discussion in the comments the root cause of the issue is:

@Shadow yes you are right. The data isn't importing from the excel into the correct fields in the temporary table. But the excel columns are an exact match to the columns in the job_new table. – user123456789

The reason for the columns not correctly being imported is in the load data command, specifically, in the list of columns provided at the end of the command:

...
FIELDS TERMINATED BY ','
(JobNo,CustManRef); 

The JobNo,CustManRef instructs MySQL to import the 1st and 2nd column from the text file into JobNo,CustManRef columns of the target table. Based on the screenshot from the excel file, the 1st and 2nd columns are the id and jobno columns.

You should remove this cluse from the load data statement and MySQL will be able to import the data in the right fields.