0

I am fetching data from an excelsheet using LOAD DATA INFILE statement and trying to evaluate the results before saving to a database. I need to be able to pass one of the fields from the excel as a mysql variable in the AGAINST() function like AGAINST(@column4) while using MATCH() AGAINST(), but it's not working.

Here's what the code looks like:

    LOAD DATA LOCAL INFILE "'.$uploadedLoanFile.'"
    IGNORE
    INTO TABLE loans_tbl
    FIELDS TERMINATED BY ","
    LINES TERMINATED BY "\n"
    IGNORE 1 LINES
    (@column1, @column2, @column3, @column4, @column5, @column6)
    SET admin_id = "'.$id.'", ippis_no = @column2, f_ap_no = @column3,
    match_status =CASE
      WHEN (SELECT client_fullname FROM customer_tbl 
               WHERE MATCH(client_fullname) AGAINST(@column4 IN BOOLEAN MODE) 
               AND loan_state ='1' AND ippis_no = @column2)  IS NOT NULL THEN "Matched"
               ELSE "Mismatched"
    END,
    uploaded_ippis = @column2, uploaded_name = @column4, client_names = @column4, employer=@column5, 
deduction = @column6, settled = "0", loan_state="1"
Akina
  • 39,301
  • 5
  • 14
  • 25
Peter
  • 60
  • 8
  • I think it is better to fill this column with separate query executed immediately after data loading. – Akina Jul 02 '21 at 11:43
  • @Akina please can you explain – Peter Jul 02 '21 at 11:53
  • Load data, but do not assign `match_status` or assign it to NULL explicitly. Then execute UPDATE query which will calculate and update the values for `match_status` which are not assigned. – Akina Jul 02 '21 at 11:55
  • There are sometimes over 20,000 records to upload, that means after upload, I have to loop through each of the just uploaded records and compare the `client_names` column in the database with each of the ` @column4` column in the excelsheet to update the `match_status` column? Just thinking that may not be efficient or will it? – Peter Jul 02 '21 at 12:08
  • *I have to loop* o_O you don't know about JOIN? see UPDATE, multiple-table syntax. – Akina Jul 02 '21 at 12:18

0 Answers0