Solved

MySQL query to load data from file

Posted on 2014-01-27
1
459 Views
Last Modified: 2014-02-03
Hi,
I have the following mysql query written in a java spring application to load data from file
into the table :

"LOAD DATA LOCAL INFILE '"+ dumpFile.replace("\\", "\\\\") +
                        "' REPLACE INTO TABLE "+ table
                        +" FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'"+
                        " (origin, destination, price, dep_date, arrival_date, supplier, search_type)";

Now the table structure to which the data is being loaded have an extra column called key.
And that extra column is key = origin#destination#dep_date
but if search_type='R'
then key = origin#destination#dep_date#arrival_date

Now i need to insert this key too.
How do i modify my query to do this ?

Thanks
0
Comment
Question by:Rohit Bajaj
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39814304
you add this expression at the end of your statement:
SET key = CONCAT(origin, '#', destination ,'#', dep_date) 

Open in new window



references:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat

hope this helps
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question