Home  >  Q&A  >  body text

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

<p>I have three tables. One table is a travel distance table</p> <pre class="brush:php;toolbar:false;">create table routes ( slat int, slng int, dlat int, dlng int, distance int );</pre> <p>A table is a site table</p> <pre class="brush:php;toolbar:false;">create table sites ( id int, name varchar(100), lat int, lng int );</pre> <p>There is also a table for technicians</p> <pre class="brush:php;toolbar:false;">create table fse ( id int, name varchar(100), lat int, lng int );</pre> <p>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. </p> <p>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. </p> <p>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</p> <p>https://www.db-fiddle.com/f/mwF1iyZ7nn8rnDcE8Wstch/0</p> <p>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? </p>
P粉413704245P粉413704245433 days ago557

reply all(1)I'll reply

  • P粉546179835

    P粉5461798352023-09-04 14:09:32

    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

    reply
    0
  • Cancelreply