Update MySQL table from a local file

mysql, sql

Solution

I suggest you load your data into a temporary table, then use an `INSERT ... SELECT ... ON DUPLICATE KEY UPDATE`; for example:

CREATE TEMPORARY TABLE temptable (
  id  INT UNSIGNED NOT NULL,
  val INT,
  PRIMARY KEY (id)
) ENGINE = MEMORY;

LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO temptable FIELDS TERMINATED BY ',';

INSERT INTO my_table
SELECT id, val FROM temptable
ON DUPLICATE KEY UPDATE val = VALUES(val);

DROP TEMPORARY TABLE temptable;

Problem

I have a table in a database, and I'd like to update a column which I have offline on a local file. The file itself has two columns - an ID which corresponds to an ID column in the table, and - the actual value. I've been able to create new rows using ``` LOAD DATA INFILE 'file.txt' INTO TABLE table FIELDS TERMINATED BY ',' ``` But I'm not sure how I can specifically insert values in such a way that the ID column in the file is joined to the ID column in the table. Can someone help with the SQL syntax?

Original source