The rewritten title is: MySQL query statements suitable for data cleaning

Question

I have three tables. One table is a travel distance table

create table routes
(
  slat int,
  slng int,
  dlat int,
  dlng int,
  distance int
);

A table is a site table

create table sites
(
  id int,
  name varchar(100),
  lat int,
  lng int
);

There is also a table for technicians

create table fse
(
  id int,
  name varchar(100),
  lat int,
  lng int
);

The travel distance table is dynamically populated via Google API requests. So if there are new stops or new technicians, all new travel distances are requested through Google and stored into the route table.

Of course, it may also happen that a customer or technician leaves. In this case, the entries in the distance table remain in the database even if they are no longer used.

I want to delete these unused rows in the table. So I'm looking for a query that can remove all "non-existent" latitude/longitude combinations in the sites/technicians table, like written in the comments of this fiddle

https://www.db-fiddle.com/f/mwF1iyZ7nn8rnDcE8Wstch/0

I could write this query myself, but my only solution is to use a very large number of subqueries. But I'm wondering if there's a more efficient way and hopefully the SQL experts here can help?

Answer 1

This query should work:

delete from routes where concat (slat,'|', slng) not in (
  select concat(lat ,'|', lng) from sites 
  union  select concat(lat,'|',lng) from fse
) ;

It selects all records from tables sites and fse and deletes rows in routes table where records are not found in the subquery. You need to use two-column concat because in can only process one column. | is only used to separate values.

DB-Fiddle