CODEWITHSUNDEEP
mysqlsqlcsv
lilek3
lilek3

Reputation: 27

Insert values from CSV file into DATABASE column according to unique ID

I have a CSV file such as: file.csv

id,firstName
1234,john
2222,jane
4567,jim

and I have a table 'People' in my database such as:

| id       | lastName       |
| -------- | -------------- |
| 1234     | doe            |
| 4567     | smith          |
| 2222     | doe            |

I would like to add a new column 'firstName' to the table and use the values from the CSV file with the unique id number,

| id       | lastName       | firstName     |
| -------- | -------------- | --------------
| 1234     | doe            | john          |   
| 4567     | smith          | jim           |   
| 2222     | doe            | jane          |

Is there a way I can achieve this?

Thank you!

Upvotes: 0

Views: 362

Answers (1)

Barmar
Barmar

Reputation: 780889

Load the CSV file into a temporary table, which you then merge into the People table.

CREATE TEMPORARY TABLE firstNames (
    id INT PRIMARY KEY,
    firstName VARCHAR(100)
);

LOAD TABLE INFILE "file.csv"
INSERT INTO firstNames;

ALTER TABLE People ADD COLUMN firstName VARCHAR(100) DEFAULT '' NOT NULL;

UPDATE People AS p
JOIN firstNames AS f ON p.id = f.id
SET p.firstName = f.firstName;

Upvotes: 1

Related Questions