Re: update two columns with LOAD INTO, all others set to NULL

[Devart Team]

UPDATE statement allows to change somw fields -
UPDATE Syntax
http://dev.mysql.com/doc/refman/5.1/en/update.html

For example -

UPDATE
  catalogue, temp_catalogue
SET
  catalogue.des = temp_catalogue.des, catalogue.yr = temp_catalogue.yr
WHERE
  catalogue.ref = temp_catalogue.ref;

But how are you going to insert new rows? So, you could use REPLACE statement with 'IGNORE' clause.


Here it is an example -

File 'data.csv' -
1;text10;
5;text50;


CREATE TABLE table1(
  column1 VARCHAR(255),
  column2 VARCHAR(255) DEFAULT NULL,
  column3 VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (column1));

INSERT INTO table1 VALUES 
  ('1', 'text1', 'text2'),
  ('2', 'text3', 'text4'),
  ('3', 'text5', 'text6');

CREATE TABLE table1_temp(
  column1 VARCHAR(255),
  column2 VARCHAR(255) DEFAULT NULL);

LOAD DATA INFILE 'data.csv.csv'
INTO TABLE table1_temp
    FIELDS TERMINATED BY ';'
    LINES TERMINATED BY '\r\n';

INSERT INTO table1(column1, column2) SELECT column1, column2 FROM table1_temp
ON DUPLICATE KEY UPDATE column1=VALUES(column1), column2=VALUES(column2);

SELECT * FROM table1;
+---------+---------+---------+
| column1 | column2 | column3 |
+---------+---------+---------+
| 1       | text10  | text2   | -- updated row
| 2       | text3   | text4   |
| 3       | text5   | text6   |
| 5       | text50  | NULL    | -- added row
+---------+---------+---------+

Devart Company,
MySQL management tools
http://www.devart.com/dbforge/mysql/