Import specific columns from text-file into mysql with matching existing value

Question

Trying to understand, is there any way to Import specific columns from text-file into mysql with matching existing value in the table.

Ex: I have a table, Ref_by and city does not have any values, wanted to load data from txt file. For values id 1 - Ref_by is 'Name1' and city 'Mumbai' & id 2 - Ref_by is 'Name2' and city 'Chennai'

drop table table1;

create table table1 (id int, name varchar(50),Ref_by varchar(50),Dept varchar(50), city varchar (100));
insert into table1 (id,name,Dept) values (1,'a','IT');
insert into table1 (id,name,Dept) values (2,'b','IT');

select * from table1;

If I load: it is going to as next rows, not sure how to match ID column from MYSQL to text files. Is it possible?

LOAD DATA INFILE 'K:/Load/db/table1_columns.txt' INTO TABLE table1 FIELDS TERMINATED BY '|' ENCLOSED BY ''  (Ref_by,city);

Needed result:

1 a name1 IT Mumbai 
2 b name2 IT Chennai

Answer 1

You can do this by first loading data into a temp or data holding table:

e.g.

LOAD DATA INFILE 'K:/Load/db/table1_columns.txt' INTO TABLE hold_table1 FIELDS TERMINATED BY '|' ENCLOSED BY ''  (Ref_by,city);

Then use join to match values from 'hold_table1' with target table 'table1' and either ADD or Update records accordingly per your condition

Answer 2

You must load your data in separate table, static or temporary, and update current table with another query.

In your case this can be:

-- create temptable for data loading
CREATE TEMPORARY TABLE tmp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_by VARCHAR(255),
    city VARCHAR(255)
    ) ENGINE = Memory;

-- load data from CSV to this table
LOAD DATA INFILE 'K:/Load/db/table1_columns.txt' 
INTO TABLE table1 
FIELDS TERMINATED BY '|'
LINES TERMINATED BY `\r\n`
(Ref_by,city);

-- update working table
UPDATE table1 
JOIN tmp USING (id)
SET table1.ref_by = tmp.ref_by,
    table1.city = tmp.city;

id AI PK column in temptable provides imported rows enumeration which is used in UPDATE in rows matching criteria.

If id values in destination table table1 contains holes then you'd enumerate the rows in this table and use this number in joining criteria.