1 Topic by Andy Lear

  • Andy Lear
  • Member
  • Offline
  • Registered: 06-06-2011
  • Posts: 25

Topic: Deleting records

Does anyone have a batch update which will delete all records imported into a Recorder 6 survey on a specific day? The survey as a lot of other records entered on earlier occasions which I want to keep.
Thanks

2 Reply by MikeWeideli

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

Re: Deleting records

The attached template is based on Batch Update JNCCDel5. It requires the Survey key and the entry_date to be changed to the appropriate key/date as required. 

That is change  LC00000100000003 to the appropriate survey key and  2014-03-27 to the appropriate date in the WHERE clause.

where Sv.Survey_Key = 'LC00000100000003' AND Datediff(d,TOCC.Entry_Date,'2014-03-27') = 0

Please take a backup and check the results carefully.

Post's attachments

JNCCDel5RecordsSpecial.xml 8.38 kb, 12 downloads since 2014-03-28 

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

MikeWeideli's Website

3 Reply by Andy Lear

  • Andy Lear
  • Member
  • Offline
  • Registered: 06-06-2011
  • Posts: 25

Re: Deleting records

Mike,
I've tried this  having made the appropriate changes and get the following message:
There is an error in the SQL code. The error message is: 'The DELETE statement conflicted with the REFERENCE constraint "FK_SURVEY_EVENT_SURVEY". The conflict occured in the database "NBNData", table "dbo.SURVEY_EVENT", column 'SURVEY_KEY"
What do I do next?
Thanks, Andy

4 Reply by MikeWeideli

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

Re: Deleting records

Will take a look and get back to you.  The update shouldn't be trying to delete the Survey and can't think why it is.

Mike Weideli

MikeWeideli's Website

5 Reply by MikeWeideli

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

Re: Deleting records

Just to say, I haven't  forgotten about this  and I am still working  it.

Mike Weideli

MikeWeideli's Website

6 Reply by Andy Lear

  • Andy Lear
  • Member
  • Offline
  • Registered: 06-06-2011
  • Posts: 25

Re: Deleting records

Mike I have managed to delete the data by moving the records into a new empty survey as described in this link: http://forums.nbn.org.uk/viewtopic.php? … 47#p19447. I then then deleted the survey using the batch update JNCC Del5records. This works because all the records that I wanted to delete from the original survey were from one single year and the survey contained no other data for that year. Thanks for your help.