Mysql – How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

MySQLmysql-5mysql-5.1mysql-5.5trigger

I have two tables 1. Customer_details and 2. Order_details.

1.Customer_details table –>fields–> Cust_Id(Primary Key) and Customer_Name,

2.Order_details table –> 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.

Best Answer

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.

Related Solutions

Mysql – Help the database isn’t performing fast enough! 100M Merge with 6M need < 1 hour!

If your table has a table with more than 1000 columns, it cannot be converted to InnoDB. In that case, run this query

SELECT CEILING(SUM(index_length)/POWER(1024,2)) num
FROM information_schema.tables WHERE engine='MyISAM';

This will give you the correct size for key_buffer_size in MB.

Since you are doing an UPSERT, you should set concurrent_insert to 2 to make INSERTs go faster. You may want to consider changing the table's row format to Fixed. I wrote about why to do both in StackOverflow. In essence, if you make the table's row format Fixed, all table rows are the same size. Thus, INSERTs and UPDATEs would operate on the exact same length of data. Management of row access is far more reasonable.

Since MyISAM only caches indexes (in the key buffer), all data must be read from disk. anything you can do to getting better RAID performance (as asked by @TomTom) would help your cause as well.

MySQL – How to Preserve ID Generated from PRIMARY KEY When Moving Data

When you have a Primary Key with an auto_increment it will generate a new ID only if you insert a NULL value. If you set ID=4 in your INSERT, the ID will be 4 so you'll not loose your ID during your "move" operation.

We don't have the "SEQUENCE" notion like in Oracle database so your "global ID" problem it's not so easy to do.

Maybe you can try something like this (but it'll add complications for just a 4 millions rows table)

Create a table used for generates your "Global ID", with one int filed auto_incremented:

CREATE TABLE test.sequence_table (next_id int primary key auto_increment);

When you want insert a new row in your child table:

Solution 1: With SELECT in information_schema

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=(auto_increment - 1) FROM information_schema.tables WHERE table_schema="test" AND table_name="sequence_table"; -- Here I use a MySQL Variable but you can store it in PHP or whatever

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)

Edit after ypercube comment:

Solution 2: With LAST_INSERT_ID()

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=LAST_INSERT_ID(); -- Use of the MySQL function LAST_INSERT_ID()

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)

Best Answer

Related Solutions

Mysql – Help the database isn’t performing fast enough! 100M Merge with 6M need < 1 hour!

MySQL – How to Preserve ID Generated from PRIMARY KEY When Moving Data

Related Question