This is a continuation on my other question link.
I have a table structure like this:
id
assigned_by
assigned_to_user_id
vendor_id
receiver_no
purchase_order_no
destination
po_status
invoice_no
invoice_amount
delivery_date
datetime_done
latest_jda_sync_date
latest_mobile_sync_date
created_at
updated_at
deleted_at
And the content of my csv file is like this:
vendor_id receiver_no purchase_order_no destination po_status
30105 20110 10151 9005 3
50015 20114 10155 9005 3
My problem here is that I have a polling/daemon process which pull data and insert them in a csv file. So I know for sure that I'll be getting duplicate data per csv. And I want to avoid that.
I'd read that using IGNORE can do this validation before my data gets inserted but my problem is that my primary key isn't 'id' and the content I'm pulling and storing in the csv file doesn't have the field 'id' in it.
The unique field in my csv is the 'receiver_no' or 'purchase_order_no'. So how do I set the IGNORE command to the po_no or receiver_no. Is this possible?
thanks,
Define a suitable UNIQUE key in your table:
ALTER TABLE my_table ADD UNIQUE KEY (receiver_no)
Add either IGNORE (to keep the existing data) or REPLACE (to delete the record and replace with the new data) after the filename to your LOAD DATA INFILE command.