- Eenty Relationship Diagram ( ERD ) review and Design
- SQL tuning
- Trigger / Function / Procedure Development
- Website Migration
- Batch Data Jobs
- Loading Data into RDBMS
- ETL Extraction Loading and Transformation between databases
You need to update a table values based upon a CSV ( comma, seperated, values )!
You need to update a table values based upon a CSV (comma, separated, values)! Complication the data extraction doesn’t include the primary key, instead it contains a primary key to a different table:
You need to update the Orders tables data based on the Report tables foreign key which will give you a way to join to the Orders table!
You have a couple of options:
1. Load the data from the CSV, into a table and write an update statement to join three tables together. Depending on the size of the file and if you need to trap for specific errors on records this could be an effective way to go! If you need to trap for various error conditions creating a function to load the data and report back on any error condition is a solid option.
2. You could convert your CSV file into an update statement like:
update Orders o set TrackingNumber ='se-101846177' , ReturnTrackingNumber = 'se-101846204' where ReportId in ( select ReportId from Reports where accession =113891);
The above is a valid SQL, and works except it takes about 40 seconds to execute in the environment I was working in. ( this was a MySql 5.1.73 Source distribution )
3. Or you can write a more elaborate piece of SQL:
update Orders inner join Reports on Reports.Reportid = Orders.Reportid set Orders.TrackingNumber ='se-99999525' and Orders.ReturnTrackingNumber = 'se-99999606' where Reports.accession =113276;
The above ran in about 23 seconds, it took longer to write but saved execution time, and databases resources.
If you need any help loading data into your RDBMS please don't hesitate to contact us!
Data Services A Washington State LLC 2018