CODEWITHSUNDEEP
mysqlsql
genekogan
genekogan

Reputation: 721

Update MySQL table from a local file

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?

Upvotes: 9

Views: 31267

Answers (3)

eggyal
eggyal

Reputation: 125855

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;

Upvotes: 11

PandeyTheDBA
PandeyTheDBA

Reputation: 1

It depends of the no of rows , If it is in hundreds make a script of update column and run it , but if it is in large volume import that file in to a new table and update your table with a join , and then drop the table

Upvotes: 0

Rahul
Rahul

Reputation: 77866

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"

Upvotes: 7

Related Questions