1 Topic by SteveGoddard

  • SteveGoddard
  • SteveGoddard
  • Member
  • Offline
  • From: Brecon
  • Registered: 31-01-2012
  • Posts: 39

Topic: Batch Update - Delete Taxon Occurences based on a csv file - error

Hi All,
We need to delete a load (30,000) duplicate records. We have created a CSV file of all the taxon occurence keys - just a simple list - no headers.

We are using the 'run batch update' called 'Delete Taxon Occurence based on csv file.

However, we are getting the following error (attached):

There is an error in the SQL code. The error message is: 'The DELETE statement conflicted with the REFERENCE constraint "FK_TAXON_OCCURENCE_SAMPLE"....

Any suggestions on how to proceed? is there a better way of deleting these records? Can we go in and manually delete them from the TAXON_OCCURENCE table? :o Would this break any relationships?

Many thanks,
Steve

Post's attachments

r6 csv import error.jpg 26.23 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
=====================================================
Steve Goddard - IT Officer
Biodiversity Information Service for Powys & Brecon Beacons National Park
T: 01874 610881 - E: steve[at]b-i-s.org - W: www.b-i-s.org

SteveGoddard's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Batch Update - Delete Taxon Occurences based on a csv file - error

You will not be able to just delete the Occurrences without deleting the data in the tables which use the key, which is what the Batch update should be doing.  The error message suggest that after deleting  occurrences  the Batch Update is then trying to delete the Samples ( I assumw where there are no occurrences left)  and this is where it is failing. I will take a look and get back to you.

Mike Weideli

MikeWeideli's Website

3 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Batch Update - Delete Taxon Occurences based on a csv file - error

Could you plesae attach the Batch Update you are using or email it to me (mike@Lfield.co.uk). I know it is one of mine, but I can't seem to find it.

Mike Weideli

MikeWeideli's Website

4 Reply by SteveGoddard

  • SteveGoddard
  • SteveGoddard
  • Member
  • Offline
  • From: Brecon
  • Registered: 31-01-2012
  • Posts: 39

Re: Batch Update - Delete Taxon Occurences based on a csv file - error

MikeWeideli wrote:

Could you plesae attach the Batch Update you are using or email it to me (mike@Lfield.co.uk). I know it is one of mine, but I can't seem to find it.

Thanks for the quick reply Mike. XML file attached.

I have made a little progress. I spotted on another thread that there is a limit of 3,000 records per file. Bit of a pain as this is 30,000 records! Anyhow, chopped it up into 10 files of which 7 imported fine. The other 3 still generate the error message.

For one of the failing files I kept chopping the file in half and trying the import again. It took ages but i eventually got down to 10 records of which at least one was causing the problem. I then deleted these manually - all worked fine! Argh!

If you can fix the batch job so I don't have to do this on the other 2 files I would be very grateful!

Steve

Post's attachments

JNCCDel7Record.zip 1.39 kb, 5 downloads since 2013-02-26 

You don't have the permssions to download the attachments of this post.
=====================================================
Steve Goddard - IT Officer
Biodiversity Information Service for Powys & Brecon Beacons National Park
T: 01874 610881 - E: steve[at]b-i-s.org - W: www.b-i-s.org

SteveGoddard's Website

5 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Batch Update - Delete Taxon Occurences based on a csv file - error

I have (I hope) fixed this. New version attached. I have tested it on a few records. Please let me know if there is still a problem with a large number of records.

Post's attachments

JNCCDel7Record.xml 6.29 kb, 5 downloads since 2013-02-26 

You don't have the permssions to download the attachments of this post.
Mike Weideli

MikeWeideli's Website

6 Reply by SteveGoddard

  • SteveGoddard
  • SteveGoddard
  • Member
  • Offline
  • From: Brecon
  • Registered: 31-01-2012
  • Posts: 39

Re: Batch Update - Delete Taxon Occurences based on a csv file - error

MikeWeideli wrote:

I have (I hope) fixed this. New version attached. I have tested it on a few records. Please let me know if there is still a problem with a large number of records.

Hi Mike,
Thanks for the quick reply. Just tried this and unfortunately I'm still getting an error message - although it is a different one - so I guess that's progress!

The new error complains about the constrain "FK_SAMPLE_RECORDER_SURVEY_EVENT_RECORDER". Error attached.

Thanks again,
Steve

Post's attachments

r6 csv import error 2.jpg 27.82 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
=====================================================
Steve Goddard - IT Officer
Biodiversity Information Service for Powys & Brecon Beacons National Park
T: 01874 610881 - E: steve[at]b-i-s.org - W: www.b-i-s.org

SteveGoddard's Website

7 Reply by SteveGoddard (edited by SteveGoddard 27-02-2013 12:03:53)

  • SteveGoddard
  • SteveGoddard
  • Member
  • Offline
  • From: Brecon
  • Registered: 31-01-2012
  • Posts: 39

Re: Batch Update - Delete Taxon Occurences based on a csv file - error

Hi Mike,
Just a quick update: I went through another CSV file that was failing and by splitting it again and again found the record that was causing the problem. The record deleted fine manually - but was the last record in the 'observation' so I was able to delete the observation as well. Wondering if this is where the batch update is failing - on records where the observation needs deleting as well??

Steve

=====================================================
Steve Goddard - IT Officer
Biodiversity Information Service for Powys & Brecon Beacons National Park
T: 01874 610881 - E: steve[at]b-i-s.org - W: www.b-i-s.org

SteveGoddard's Website