1

Re: How to batch delete based on a list of TOKs

I want to delete 100+ records from a much larger survey. These records are mostly in separate survey events, so I cant see a way to use the existing batch delete.

What would really do the trick is a batch update that would delete the records (and associated samples, survey events as appropriate) based on a text file containing the TOKs which I can get easily enough with a report.

Is there a batch update in existence which would do the trick? (Or another way of doing it that I've missed?)

Rich

Richard Burkmar
Biodiversity Project Officer
Field Studies Council

2

Re: How to batch delete based on a list of TOKs

Rich

I have a batch update which does this. Internet issues mean I can't put it on the forum at the moment, but it is available from www.Lfield.co.uk/downloads/JNCCDel7Record.zip  You need a csv file with a column Taxon_Occurrence_Key which you can generate and save via the report wizard. The batch update will be in the Delete folder and named DEL7.  It will delete the Taxon Occurrences and anything associated  with them. It will then delete the  samples assuming all the TOCCs have gone and there are no biotopes. It will then delete the Survey Events if all the samples have gone. As always please back up your database and check the results before adding new data. I think it works but let me know if it gives you any problems.

Mike

Mike Weideli

3

Re: How to batch delete based on a list of TOKs

As ever Mike, thanks for your help. The batch update sounds perfect. I will have a go with it on Thursday.

Rich

Richard Burkmar
Biodiversity Project Officer
Field Studies Council

4

Re: How to batch delete based on a list of TOKs

Mike, what is the significance of 3000? It looks like you're processing in batches of 3000 records at a time and when the @@rowcount drops below 3000 on an iteration (it must therefore be the last iteration), it breaks. I was just wondering what the thinking is behind this - is it that you've found 3000 to be a reliable figure to work with?

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

5

Re: How to batch delete based on a list of TOKs

Charles

Trial and error on my slowest system running MSDE, following problems with deleting large amounts of data. Could probably be changed for systems with more memory and or later versions of SQL Server , which may speed it up.       

Mike

Mike Weideli

6

Re: How to batch delete based on a list of TOKs

Hi Mike,

Strange, but I'm getting an error message when I try to run this. I've simply unzipped the file you pointed to and not edited it in any way. It asks me for the CSV which I locate. Then it falls over with a message box showing these two lines:

The following error occurred while attempting to run the batch SQL:
An expression of non-boolean type specified in a context where a condition is expected near 'UPDATE'

I had a look at the SQL, in the batch file but I can't see anything wrong. Can't find the temporary table #LCDelete in  the SQL server DB, but I suppose that Recorder/SQL Server may have rolled the whole transaction back and deleted that?

I don't think it can be anything to do with permissions since I've run other deletion batch updates before without any problem.

Any ideas?

Rich

Richard Burkmar
Biodiversity Project Officer
Field Studies Council

7

Re: How to batch delete based on a list of TOKs

Rich

My fault. there are errors in the update statements. Must have saved the wrong version at some point. Have tested and new version is at   http://www.lfield.co.uk/downloads/JNCCDel7Record.zip.

Mike

Mike Weideli

8

Re: How to batch delete based on a list of TOKs

Thanks Mike - it worked fine this time.

Richard Burkmar
Biodiversity Project Officer
Field Studies Council

9

Re: How to batch delete based on a list of TOKs

I have a related querie.  I need to delete a subset of taxa from within larger surveys.  However, the additional requirement is that I only want to delete records occuring within a Vice-county.  We have digitised vice-county boundaries.
I am fairly new to this so don't know all the technicalities, but if there is a batch file that would do this it would be great!

10

Re: How to batch delete based on a list of TOKs

Hello,

Is this batch delete thing still available?  Mike's link above now goes to a 404 page.  If the utility is still around, please could it be flagged so I can get a copy?  Many thanks!

Regards
Claire

11

Re: How to batch delete based on a list of TOKs

The file is still there, but the extra full stop on the end of the link is causing the problem. Try

  http://www.lfield.co.uk/downloads/JNCCDel7Record.zip

Mike Weideli

12 (edited by EcoRecord_Andy 13-09-2017 15:14:58)

Re: How to batch delete based on a list of TOKs

Hi Mike,

I tried running the Batch Update ‘JNCCDel7Record’ and I received the following error message:

There is an error in the SQL code.  The error message is: ‘The DELETE statement conflicted with the REFERENCE constraint “FK_TAXON_OCCURRENCE_SAMPLE”. The conflict occurred in database “NBNData”, table “dbo.TAXON_OCCURRENCE”, column ‘SAMPLE_KEY”

The csv file I used contained one column titled Taxon_Occurrence_Key containing around 9,000 Taxon_Occurrence_Keys of (duplicate) records which I need to delete.
Could you please advise how I can resolve this?

*edit* I've managed to run it successfully now using a smaller set of TOKs

Many thanks,
Andy

Biodiversity Information Officer
EcoRecord