How to Import Data from a CSV File and Update Tables in MySQL

Managing data efficiently is crucial for any application. MySQL offers a powerful way to import bulk data directly from CSV files and also allows you to update one table based on the values from another. This article walks you through both processes step by step, with examples and explanations.

Part 1: Importing CSV Data into MySQL

If you have a large dataset in a CSV file and need to insert it into a MySQL table, use the LOAD DATA LOCAL INFILE statement. It is much faster than inserting rows one at a time.

Example SQL Command

LOAD DATA LOCAL INFILE '/var/www/html/csv_file/your_csv_file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@col1, @col2, @col3, @col4, @col5)
SET
  col1 = @col1,
  col2 = @col2,
  col3 = @col3,
  col4 = @col4,
  col5 = @col5;

 Step-by-Step Explanation

1. LOAD DATA LOCAL INFILE: Loads data from the file path you specify. The LOCAL keyword allows you to read a file located on the client system (not necessarily the server).
2. INTO TABLE my_table: Target table where the data will be inserted. Replace my_table with the actual table name.
3. FIELDS TERMINATED BY ‘,’: Each field (column) in the CSV is separated by a comma.
4. LINES TERMINATED BY ‘\n’: Each row is separated by a newline character.
5. IGNORE 1 ROWS: Skips the header row in the CSV (often containing column names).
6. (@col1, @col2, …): Loads data into variables so you can manipulate or sanitize them before inserting.
7. SET: Assigns the variables to actual table columns. Useful for custom formatting or ignoring columns from the CSV.

Tip:

Make sure the MySQL user has the FILE privilege, and that the MySQL client allows LOCAL INFILE. Also, your table schema should match the CSV format.

---

Part 2: Updating Data Based on Another Table

Often, you may need to sync or enrich data from another table. This is typically done using a SQL UPDATE query with a JOIN.

Example SQL Command

UPDATE table_1
INNER JOIN table_2 ON table_1.internal_id = table_2.internal_id
SET
  table_1.city = table_2.city,
  table_1.state = table_2.state;

Step-by-Step Explanation

• INNER JOIN: Combines rows from table_1 and table_2 where the internal_id matches in both tables.
• SET: Updates city and state in table_1 using values from table_2.
• WHERE: Not needed here because the INNER JOIN ensures only matching records are affected.

Use Case Example:

Suppose table_1 contains user profiles and table_2 contains updated city/state info fetched from a third-party source. This query helps you update your main table without manual effort.

---

Conclusion

Using LOAD DATA INFILE is the fastest way to import large datasets into MySQL. Pair it with update queries using joins to maintain clean, up-to-date, and synchronized databases. This workflow is essential for data migrations, syncing with external sources, or initial project setup.

Got questions or need help troubleshooting errors during CSV import or table joins? Let me know!