How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger I have two tables 1. Customer_details and 2. Order_details.

1.Customer_details table --&gt;fields--&gt; Cust_Id(Primary Key) and Customer_Name,
2.Order_details table --&gt; fields-- Ord_Id(Primary Key), Cust_id

(There is no field for Customer name in Order_details table). I am trying to import customer orders to order_details from a CSV file through an external application.  The CSV File contains the customer name not the Cust_ID. When inserting into order_details I need to look up the Cust_ID based on the name in the file and insert the Cust_ID into order_details instead. This needs to be implemented at the Database level not in the application code. I have been searching a lot for answers but cant figure it out. Could I possibly use a trigger to accomplish this?
Thanks, Acube.
Assuming that Customer_details table is populated you can do lookups on the fly with LOAD DATA INFILE by leveraging session variables and a SET clause

LOAD DATA INFILE '/path/to/order_details.txt'
INTO TABLE order_details
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ord_id, @customer_name) -- use a session variable to store a value read from the file
SET cust_id = 
(
  SELECT cust_id
    FROM customer_details
   WHERE customer_name = @customer_name -- use a session variable to get an id
   LIMIT 1 -- make sure that exactly one row is returned
) 


Suppose we have a CSV file with the following content


ord_id, customer_name
1,"Customer1"
2,"Customer2"
3,"Customer2"
4,"Customer4"


Let's try import it:


mysql> CREATE TABLE customer_details(`cust_Id` int, `customer_name` varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Customer_details (`cust_Id`, `customer_name`)
    -> VALUES (1, 'Customer1'),(2, 'Customer2'),(3, 'Customer3'),(4, 'Customer4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE order_details (`ord_id` int, `cust_Id` int);
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA INFILE '/tmp/order_details.txt'
    -> INTO TABLE order_details
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (ord_id, @customer_name) -- use a session variable to store a value read from the file
    -> SET cust_id =
    -> (
    ->   SELECT cust_id
    ->     FROM customer_details
    ->    WHERE customer_name = @customer_name -- use a session variable to get an id
    ->    LIMIT 1 -- this is to ensure that exactly one is returned
    -> ) ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM order_details o JOIN customer_details c ON o.cust_id = c.cust_id;
+--------+---------+---------+---------------+
| ord_id | cust_Id | cust_Id | customer_name |
+--------+---------+---------+---------------+
|      1 |       1 |       1 | Customer1     |
|      2 |       2 |       2 | Customer2     |
|      3 |       2 |       2 | Customer2     |
|      4 |       4 |       4 | Customer4     |
+--------+---------+---------+---------------+
4 rows in set (0.00 sec)


There you have it.