从本地文件更新MySQL表 [英] 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. 与表中的ID列相对应的ID，以及
2. 实际值.

我已经能够使用

LOAD DATA INFILE 'file.txt' INTO TABLE table
  FIELDS TERMINATED BY ','

但是我不确定如何特别地插入值，以使文件中的ID列与表中的ID列结合在一起.有人可以提供有关SQL语法的帮助吗?

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?

推荐答案

我建议您将数据加载到临时表中，然后使用INSERT ... SELECT ... ON DUPLICATE KEY UPDATE;例如:

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;

这篇关于从本地文件更新MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！