More

Deleting records from table where ST_DIstance from another record in another table is greater than a certain amount

Deleting records from table where ST_DIstance from another record in another table is greater than a certain amount


I'm currently learning SQL on the run, and have come across a situation where I'm trying to delete records from a table depending on a function result. I can select the records of interest with the following query -

select ST_Distance(a.reporter_location, b.report_location) as dist, b.hex, b.flight, b.report_timestamp, b."isMLAT" from reporter a, planereports b where a.name like 'Home1%' and ST_Distance(a.reporter_location, b.report_location) > 400000.0 order by b.report_timestamp;

but I'm unsure how to place this into a conditional for a delete statement. Any ideas? Brain is turning to mush.


You can use your select statement as input for the where clause of the delete statement. Hava a look at the docs of delete.

delete from planereports where hex in (select b.hex from reporter a, planereports b where a.name like 'Home1%' and ST_Distance(a.reporter_location, b.report_location) > 400000.0 );