CODEWITHSUNDEEP
mysqlsql
James C.
James C.

Reputation: 37

Transaction to load data infile and then update columns

I'm trying to load new rows into a table and then update other columns in the table with the uploaded IDs. I'm only uploading IDs and then joining other tables to pull other data into the table. Query I tried:

START TRANSACTION
LOAD DATA LOW_PRIORITY LOCAL INFILE 'file path' REPLACE INTO 
    TABLE
        tablex CHARACTER SET
        latin1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`date_orderable`,
        `id1`,
        `id2,
        `id3,
        `d1`,
        `d2,
        `d3,
        );
update tablex a left join uber_data b on a.id1 = b.id1 
set a.d1 =b.d1;
update tablex a left join uber_data b on a.id2 = b.id2 
set a.d2 =b.d2;
update tablex a left join uber_data b on a.id2 = b.id1 
set a.d3 =b.d3;
COMMIT

It says there's an error in line 2 when running. Anyone know how I can load data into the table and then update the new rows? Thanks!

Upvotes: 2

Views: 540

Answers (1)

GMB
GMB

Reputation: 222702

There are several syntax errors in your query :

  • there are unbalanced backticks around some of the fields in the list (like d2,</code> should be <code>d2`,

  • a semicolon is missing at the end of the START TRANSACTION; statement, and at the end of the COMMIT; statement as well. Without the semicolon, MySQL tries to parse two statements at once, which generates a syntax error

Upvotes: 1

Related Questions