Insert data using a CSV file

LOAD DATA LOCAL INFILE 
    '/var/www/html/csv_file/your_csv_file.csv' 
IGNORE 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

Let’s break down the code step by step:

1. LOAD DATA LOCAL INFILE '/var/www/html/csv_file/your_csv_file.csv':This part of the code specifies the path to the CSV file you want to load data from. In this example, the file path is ‘/var/www/html/csv_file/your_csv_file.csv’. The LOCAL keyword indicates that the file is located on the local file system of the MySQL client.
2. INTO TABLE my_table:This part of the code specifies the target database table where you want to insert the data. Replace my_table with the name of your actual MySQL table.
3. FIELDS TERMINATED BY ',':This part of the code specifies that the fields in the CSV file are separated by a comma (,). This delimiter is used to split each line of the CSV file into individual columns.
4. LINES TERMINATED BY '\n':This part of the code specifies that each line in the CSV file is terminated by a newline character (\n). This indicates the end of a row in the CSV file.
5. IGNORE 1 ROWS:This optional part of the code tells MySQL to ignore the first row of the CSV file. This is often used when the first row contains headers and you don’t want to insert it as data.
6. (@col1, @col2, @col3, @col4, @col5):This part of the code defines user variables @col1, @col2, @col3, @col4, and @col5. These variables correspond to the columns in your MySQL table.
7. SET col1 = @col1, col2 = @col2, col3 = @col3, col4 = @col4, col5 = @col5:This part of the code sets the values of the table columns (col1, col2, col3, col4, col5) to the values stored in the user variables (@col1, @col2, @col3, @col4, @col5) for each row of data in the CSV file. This effectively maps the columns in the CSV file to the corresponding columns in the database table.

Update data based on another table

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
WHERE
    table_2.internal_id = table_2.internal_id