1 Topic by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Topic: Delete Events

Hi

I have moved >700 samples that were all under their own event to fall into one event (using management studio) and need to get rid of the remaining empty events. Trying to delete one in Recorder doesn't work as it tells me that it has records linked to it, but I am not sure what these would be as it no longer has any samples.

I could run a delete in management studio, but want to make sure this won't cause any further problems.

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

2 Reply by MikeWeideli

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

Re: Delete Events

Probably Survey_Event_Recorder is the issue. No harm in deleting the entries in this table for the events in question.

Mike Weideli

MikeWeideli's Website

3 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Delete Events

Looks like you are right Mike. The delete fails because of a conflict with FK_SURVEY_EVENT_RECORDER_SURVEY_EVENT in SURVEY_EVEN_RECORDER. Looks like I need to work out what to delete in that table first.

Gordon Barker
Biological Survey Data Manager
National Trust

4 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Delete Events

OK, I can't delete in SURVEY_EVENT_RECORDER because of another conflict with SAMPLE_RECORDER. Need to think some more about this.

Gordon Barker
Biological Survey Data Manager
National Trust

5 Reply by MikeWeideli

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

Re: Delete Events

I had thought the Samples_recorders had to go before the sample, but obviously not. You can delete all Sample_Recorder entries where there are no samples.  Try

SELECT *  FROM SAMPLE_RECORDER
WHERE NOT EXISTS (SELECT * FROM SAMPLE WHERE SAMPLE.SAMPLE_KEY =
SAMPLE_RECORDER.SAMPLE_KEY)

then  if you are happy

DELETE FROM SAMPLE_RECORDER
WHERE NOT EXISTS (SELECT * FROM SAMPLE WHERE  SAMPLE.SAMPLE_KEY =
SAMPLE_RECORDER.SAMPLE_KEY)

You could use te same approach to remove  Survey events with no samples, but may want to restrict this
to a particular Survey,

DELETE FROM SURVEY_EVENT WHERE SURVEY_EVENT.SURVEY_KEY  = 'XXXXXXXXXXX'
AND NOT EXISTS (SELECT * FROM SAMPLE WHERE SAMPLE.SURVEY_EVENT_KEY =
SURVEY_EVENT.SURVEY_EVENT_KEY)

Mike Weideli

MikeWeideli's Website

6 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Delete Events

Thanks Mike

That put me on the right track. I think that I had updated the Sample table, changing all the sample SURVEY EVENT KEYs to the new value but hadn't done it in SURVEY EVENT RECORDER. With that done I could run the 3rd script to delete the Events. The second one also led me to find some other events that are also childless which I need to investigate

Gordon Barker
Biological Survey Data Manager
National Trust