How do I selectively delete a large number of points?

[Vlasta]

I need to selectively remove a large portion of data points (~50k points, so doing it manually is not an option).

I exported all my points as a csv file, and separated ones I want to keep, and ones I want to purge.

I was hoping I could remove points via csv file import/export, but this does not seem to be possible. What is the best way to do a selective point deletion? Is it possible (and advisable) to delete directly from the database?

Optionally, since I don't yet have any data associated with these points, could I delete the whole lot, and use csv import to only bring back points I need? If so, how would I delete it?

[phildunlap]

Hi Vlasta, you are correct in thinking you could delete everything and then recreate what is desired via the importer, but this would lose point value histories.

I would do this by massaging your CSV of the points you wish to delete into a list of XIDs and then doing it on the SQL console. So, you have an 'xid' column in your CSV, and if you can get it into the format: ('xid1', 'xid2', .... ) you can do something like this at the SQL console....

DELETE FROM dataPoints WHERE xid IN ('DP_012345', 'DP_123456', .... 'DP_567890');

[Jared Wiltshire]

Note that deleting the data points using the SQL console will not delete the point values straight away. However these will be deleted when the data purge occurs, usually nightly.

[Vlasta]

Thanks, both answers helped a lot.
Is there a way to make System Settings -> 'Purge now...' remove more than 100000 records per run?

[Jared Wiltshire]

Unfortunately there is no way.

You can also do it from the SQL console, try this
DELETE FROM pointValues WHERE dataPointId NOT IN (SELECT ID FROM dataPoints) LIMIT xxx