<![CDATA[Forum — Error with Del7 - is there another way of batch deleting records?]]> https://forums.nbn.org.uk/viewtopic.php?id=8087 Wed, 01 Jul 2026 10:22:04 +0000 PunBB 1.4.6 <![CDATA[Re: Error with Del7 - is there another way of batch deleting records?]]> https://forums.nbn.org.uk/viewtopic.php?pid=31515#p31515 AMAZING!!! Someone buy that man a pint :)

]]> Wed, 01 Jul 2026 10:22:04 +0000 https://forums.nbn.org.uk/viewtopic.php?pid=31515#p31515 <![CDATA[Re: Error with Del7 - is there another way of batch deleting records?]]> https://forums.nbn.org.uk/viewtopic.php?pid=31514#p31514 Here is a new version of the XML that includes deleting any related Taxon_Private_Data rows. Hopefully this will fix the problem.

<?xml version="1.0" ?>
<!-- Not System Supplied   

     XML Batch Update by Mike Weideli of Littlefield Consultancy
     http://www.lfield.co.uk
     Version 3 - June 2026 (amended by Andy Foy) -->
           
<batchupdate menupath="Delete" title="Del7 Delete Taxon Occurrences based on csv file."

description="This batch update deletes taxon occurrences and all the related structure.
It also deletes the Samples and Survey Events where the deletion of the Taxon Occurrence results in these having no data.
Restricted to 3000 records at a time.
See https://forums.nbn.org.uk/viewtopic.php?id=7866 for note of why it might not work.
Script: JNCCDel7Record.xml" >

<CSVTables>
          <CSVTable name="#CSV1">
          <CSVFile description="Taxon Occurrence File" />
          <CSVColumn name = "Taxon_Occurrence_Key" datatype="Char" size="16" />
          </CSVTable>

         
</CSVTables>

<SQL>
   
CREATE TABLE #LCDelete ( Survey_Event_key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
                 Sample_Key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
             Taxon_Occurrence_key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS )

<Where keytype="Default">

   INSERT INTO #LCDelete (Taxon_Occurrence_Key)
   SELECT DISTINCT Taxon_Occurrence_Key from #CSV1

   UPDATE #LCDELETE SET SAMPLE_KEY = TOCC.Sample_Key
   FROM Taxon_Occurrence TOCC
   INNER JOIN #LCDELETE ON TOCC.Taxon_Occurrence_Key = #LCDELETE.Taxon_Occurrence_Key

   UPDATE #LCDELETE SET SURVEY_EVENT_Key = S.Survey_Event_key
   FROM Sample S
   INNER JOIN #LCDELETE ON S.Sample_Key = #LCDELETE.Sample_Key
   
   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TDET
   FROM
   TAXON_DETERMINATION TDET INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TDET.TAXON_OCCURRENCE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TSC
   FROM
   TAXON_OCCURRENCE_SOURCES  TSC INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TSC.TAXON_OCCURRENCE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN   
   DELETE FROM TDAT
   FROM
   TAXON_OCCURRENCE_DATA  TDAT INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TDAT.TAXON_OCCURRENCE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TREL
   FROM
   TAXON_OCCURRENCE_RELATION  TREL INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TREL.TAXON_OCCURRENCE_KEY_1
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TREL2
   FROM
   TAXON_OCCURRENCE_RELATION  TREL2 INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TREL2.TAXON_OCCURRENCE_KEY_2
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SP
   FROM
   SPECIMEN SP INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = SP.TAXON_OCCURRENCE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TPD
   FROM
   Taxon_Private_Data TPD INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TPD.TAXON_OCCURRENCE_KEY
   IF @@Rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TOCC
   FROM
   TAXON_OCCURRENCE TOCC INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TOCC.TAXON_OCCURRENCE_KEY
   IF @@Rowcount &#60; 3000 BREAK
   END

   DELETE FROM #LCDELETE
   WHERE EXISTS(SELECT * FROM TAXON_OCCURRENCE TOCC WHERE TOCC.sample_key = #LCDELETE.SAMPLE_KEY)

   DELETE FROM #LCDELETE
   WHERE EXISTS(SELECT * FROM BIOTOPE_OCCURRENCE BOCC WHERE BOCC.sample_key = #LCDELETE.SAMPLE_KEY)

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SSC
   FROM
   SAMPLE_SOURCES SSC INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SSC.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SDAT
   FROM
   SAMPLE_DATA SDAT INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SDAT.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SAD
   FROM
   SAMPLE_Admin_Areas SAD INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SAD.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SREL1
   FROM
   SAMPLE_RELATION SREL1 INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SREL1.SAMPLE_KEY_1
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SREL2
   FROM
   SAMPLE_RELATION SREL2 INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SREL2.SAMPLE_KEY_2
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SREC
   FROM
   SAMPLE_RECORDER SREC INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SREC.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM S
   FROM
   SAMPLE S INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = S.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   DELETE FROM #LCDELETE WHERE
   EXISTS (SELECT * FROM SAMPLE S WHERE S.Survey_Event_key = #LCDELETE.Survey_Event_key)

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SSC
   FROM
   SURVEY_EVENT_SOURCES SSC INNER JOIN #LCDELETE #LCD ON #LCD.SURVEY_EVENT_KEY = SSC.SURVEY_EVENT_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SEREC
   FROM
   SURVEY_EVENT_RECORDER SEREC INNER JOIN #LCDELETE #LCD ON #LCD.SURVEY_EVENT_KEY = SEREC.SURVEY_EVENT_KEY
   IF @@rowcount &#60; 3000 BREAK
   END
   
   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SEO
   FROM
   SURVEY_EVENT_OWNER SEO INNER JOIN #LCDELETE #LCD ON #LCD.SURVEY_EVENT_KEY = SEO.SURVEY_EVENT_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SE
   FROM
   SURVEY_EVENT SE INNER JOIN #LCDELETE #LCD ON #LCD.SURVEY_EVENT_KEY = SE.SURVEY_EVENT_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   DROP TABLE #LCDELETE

</Where>

</SQL>

</batchupdate>

]]> Tue, 30 Jun 2026 14:13:30 +0000 https://forums.nbn.org.uk/viewtopic.php?pid=31514#p31514 <![CDATA[Re: Error with Del7 - is there another way of batch deleting records?]]> https://forums.nbn.org.uk/viewtopic.php?pid=31513#p31513 Thanks Andy! Here it is

<?xml version="1.0" ?>
<!-- Not SYstem Supplied   
 
     XML Batch Update by Mike Weideli of Littlefield Consultancy
     http://www.lfield.co.uk
     Version 2 - October 2010 -->
            
<batchupdate menupath="Delete" title="Del7 Delete Taxon Occurences based on csv file."

description="This batch update deletes taxon occurrences and all the related structure.
It also deletes the Samples and Survey Events where the deletion of the Taxon Occurrence results in these having no data.
Restricted to 3000 records at a time.
See https://forums.nbn.org.uk/viewtopic.php?id=7866 for note of why it might not work.
Script: JNCCDel7Record.xml" >




<CSVTables>
          <CSVTable name="#CSV1">
          <CSVFile description="Taxon Occurrence File" />
          <CSVColumn name = "Taxon_Occurrence_Key" datatype="Char" size="16" />
          </CSVTable>

         
</CSVTables>

<SQL>
   
CREATE TABLE #LCDelete (        Survey_Event_key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
        Sample_Key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
        Taxon_Occurrence_key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS
                   
           )
<Where keytype="Default">
   

   INSERT INTO #LCDelete (Taxon_Occurrence_Key)
   SELECT DISTINCT  Taxon_Occurrence_Key from #CSV1 

   UPDATE #LCDELETE SET SAMPLE_KEY = TOCC.Sample_Key
   FROM Taxon_Occurrence TOCC
   INNER JOIN #LCDELETE ON TOCC.Taxon_Occurrence_Key = #LCDELETE.Taxon_Occurrence_Key
   

   UPDATE #LCDELETE SET SURVEY_EVENT_Key = S.Survey_Event_key
   FROM Sample S
   INNER JOIN #LCDELETE ON  S.Sample_Key  = #LCDELETE.Sample_Key


   
   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TDET
   FROM
   TAXON_DETERMINATION TDET INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TDET.TAXON_OCCURRENCE_KEY
   
     
     IF @@rowcount &#60; 3000 BREAK
 
   END
 
   
   
   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TSC
   FROM
   TAXON_OCCURRENCE_SOURCES  TSC INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TSC.TAXON_OCCURRENCE_KEY
   IF @@rowcount &#60; 3000 BREAK
 
   END
 

   set rowcount  3000
   WHILE 1 =1
   BEGIN   
   DELETE FROM TDAT
   FROM
   TAXON_OCCURRENCE_DATA  TDAT INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TDAT.TAXON_OCCURRENCE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END



   set rowcount  3000
   WHILE 1 =1
   BEGIN 
   DELETE FROM TREL
   FROM
   TAXON_OCCURRENCE_RELATION  TREL INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TREL.TAXON_OCCURRENCE_KEY_1
   IF @@rowcount &#60; 3000 BREAK
   END


   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TREL2
   FROM
   TAXON_OCCURRENCE_RELATION  TREL2 INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TREL2.TAXON_OCCURRENCE_KEY_2
   IF @@rowcount &#60; 3000 BREAK
   END


   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SP
   FROM
   SPECIMEN SP INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = SP.TAXON_OCCURRENCE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END


   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM TOCC
   FROM
   TAXON_OCCURRENCE TOCC INNER JOIN #LCDELETE #LCD ON #LCD.TAXON_OCCURRENCE_KEY = TOCC.TAXON_OCCURRENCE_KEY
   
   IF @@Rowcount &#60; 3000 BREAK
   END
   
   
   DELETE FROM #LCDELETE
   WHERE EXISTS(SELECT * FROM TAXON_OCCURRENCE TOCC WHERE TOCC.sample_key = #LCDELETE.SAMPLE_KEY)
   

   DELETE FROM #LCDELETE
   WHERE EXISTS(SELECT * FROM BIOTOPE_OCCURRENCE BOCC WHERE BOCC.sample_key = #LCDELETE.SAMPLE_KEY)
   


   
   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SSC
   FROM
   SAMPLE_SOURCES SSC INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SSC.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SDAT
   FROM
   SAMPLE_DATA SDAT INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SDAT.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SAD
   FROM
   SAMPLE_Admin_Areas SAD INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SAD.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SREL1
   FROM
   SAMPLE_RELATION SREL1 INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SREL1.SAMPLE_KEY_1
   IF @@rowcount &#60; 3000 BREAK
   END


   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SREL2
   FROM
   SAMPLE_RELATION SREL2 INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SREL2.SAMPLE_KEY_2
   IF @@rowcount &#60; 3000 BREAK
   END


   

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SREC
   FROM
   SAMPLE_RECORDER SREC INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = SREC.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM S
   FROM
   SAMPLE S INNER JOIN #LCDELETE #LCD ON #LCD.SAMPLE_KEY = S.SAMPLE_KEY
   IF @@rowcount &#60; 3000 BREAK
   END


   DELETE FROM #LCDELETE WHERE
   EXISTS (SELECT * FROM SAMPLE S WHERE S.Survey_Event_key = #LCDELETE.Survey_Event_key)

   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SSC
   FROM
   SURVEY_EVENT_SOURCES SSC INNER JOIN #LCDELETE #LCD ON #LCD.SURVEY_EVENT_KEY = SSC.SURVEY_EVENT_KEY
   IF @@rowcount &#60; 3000 BREAK
   END

 
   
   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SEREC
   FROM
   SURVEY_EVENT_RECORDER  SEREC INNER JOIN #LCDELETE #LCD ON #LCD.SURVEY_EVENT_KEY = SEREC.SURVEY_EVENT_KEY
   IF @@rowcount &#60; 3000 BREAK
   END
   
   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SEO
   FROM
   SURVEY_EVENT_OWNER  SEO INNER JOIN #LCDELETE #LCD ON #LCD.SURVEY_EVENT_KEY = SEO.SURVEY_EVENT_KEY
   IF @@rowcount &#60; 3000 BREAK
   END


   set rowcount  3000
   WHILE 1 =1
   BEGIN
   DELETE FROM SE
   FROM
   SURVEY_EVENT  SE INNER JOIN #LCDELETE #LCD ON #LCD.SURVEY_EVENT_KEY = SE.SURVEY_EVENT_KEY
   IF @@rowcount &#60; 3000 BREAK
   END
   


   DROP TABLE #LCDELETE

</Where>



   


</SQL>



</batchupdate>

]]> Tue, 30 Jun 2026 09:47:48 +0000 https://forums.nbn.org.uk/viewtopic.php?pid=31513#p31513 <![CDATA[Re: Error with Del7 - is there another way of batch deleting records?]]> https://forums.nbn.org.uk/viewtopic.php?pid=31512#p31512 I will if someone can share the xml file with me.

]]> Fri, 26 Jun 2026 07:59:48 +0000 https://forums.nbn.org.uk/viewtopic.php?pid=31512#p31512 <![CDATA[Re: Error with Del7 - is there another way of batch deleting records?]]> https://forums.nbn.org.uk/viewtopic.php?pid=31511#p31511 Andy - could you post a solution/comments to this as I have been fighting with the same problem?

Bob Marsh

]]> Fri, 26 Jun 2026 07:53:40 +0000 https://forums.nbn.org.uk/viewtopic.php?pid=31511#p31511 <![CDATA[Re: Error with Del7 - is there another way of batch deleting records?]]> https://forums.nbn.org.uk/viewtopic.php?pid=31510#p31510 I don't have a Del7 batch update to check but my guess would be that it doesn't include deleting related Taxon_Private_Data records, as this is a relatively new table, and hence fails when trying to delete Taxon_Occurrence records. Can you post the xml code?

]]> Thu, 25 Jun 2026 13:15:27 +0000 https://forums.nbn.org.uk/viewtopic.php?pid=31510#p31510 <![CDATA[Error with Del7 - is there another way of batch deleting records?]]> https://forums.nbn.org.uk/viewtopic.php?pid=31509#p31509 Hi. I understand from looking through this forum that this has been an issue before! I'm trying to delete some duplicate records. I have followed these steps to create a list of records to delete:

Create a csv file containing the records you want to delete.

Change the Obs Key field to “Taxon_Occurrence_Key” and cut and paste this so that it’s the first column in the sheet

Add a field called “Determination_Type” in which all the rows must have the value “Delete”

Add a column called “Verifiers_Comments” and another one called “Scientific_name”, both of which can be blank

Delete all the other fields in the csv and close it

however, when I try to use this as the input to Del7, I get 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_PRIVATE_DATA_OCCURRENCE". The conflict occurred in database "NBNData", table "dbo.Taxon_Private_Data", column "Taxon_Occurrence_Key".

Can anyone help?

Thanks very much, Jo

]]> Wed, 24 Jun 2026 12:35:12 +0000 https://forums.nbn.org.uk/viewtopic.php?pid=31509#p31509