Topic: SQL to mark up duplicate records
It is NBN upload time again. This is the time when the number of duplicate records in our database really becomes apparent. Not just identical duplicates either but the same record with different recorders and grid reference precisions upon closer inspection i.e.
Soprano Roost, SD 123 456, 05/07/2015, ABC123 Consultancy, "Roost in west face of building under gutter"
Soprano Roost, SD 1239 4561, July 2015, John Smith, "Roost in west face of building under gutter - roost retained"
In the above case we would like to retain the second record and mark the first as a duplicate so it doesn't keep reappearing in reports. We want to retain it rather than delete it for several reasons including
* keeping the first dataset intact
* so we don't get the deleted record returned to us
* if a data exchange agreement terminates and we have to delete the dataset containing the second record we still have access to the first version obtained via a different source
Does anybody have an example query which would do the following?
i.e. UPDATE occurrence_determination SET determination = "duplicate record", determination comment = "<insert original occurence id>" WHERE taxon_occurrence.id = "<insert duplicate occurence id>"
Thanks for any ideas in advance. Although I'm fairly seasoned in SQL the recorder6 data model scares me a little.