Update MySQL table from a local file

Question

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

1. an ID which corresponds to an ID column in the table, and
2. 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?

Answer 1

Another way could be ...

Since you already know the table name as well have the ID and actual value ... what you can do is ... directly write the update statements in a file, like

update mytable set value_col = value where ID_col = ID;

Second Update Statement

Third Update statement

.......

Save the file as *.sql like, updatescript.sql and then execute that script directly like

mysql -h <hostname> -u root -p <your_db_name> < "E:/scripts/sql/updatescript.sql"

Answer 2

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;