update.sql

-- col1 主キー  (csv 第1カラム)
-- col2 値        (csv 第2カラム)
-- col3 値        (csv 第3カラム)
-- col4 登録timestamp
-- col5 更新timestamp
-- 一時テーブルの作成
DROP TABLE IF EXISTS tmp_table;
CREATE TABLE tmp_table LIKE target_table;
 
-- 一時テーブルの読込み
LOAD DATA LOCAL INFILE "/path/to/csvfile.csv" INTO TABLE tmp_file
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES  STARTING BY '' TERMINATED BY '\r\n'
  (col1, col2, col3);
 
START TRANSACTION;
-- 更新		
UPDATE target_table AS tar 
	LEFT JOIN tmp_table AS tmp ON tar.col1 = tmp.col1
	SET tar.col2 = tmp.col2,
		tar.col3 = tmp.col3,
		tar.col5 = now()
	WHERE	tar.col2 != tmp.col2 
		OR	tar.col3 != tmp.col3; 
 
-- 追加
INSERT INTO target_table (
		col1,
		col2,
		col3,
		col4,
		col5,
	)
	SELECT 
		tmp.col1,
		tmp.col2,
		tmp.col3,
		now(),
		now()
	FROM tmp_table AS tmp 
	WHERE NOT EXISTS (SELECT 1 FROM target_table AS tar WHERE tar.col1 = tmp.col1);
COMMIT;
 
-- 一時テーブルの削除
DROP TABLE IF EXISTS tmp_table;