Remove duplicate records from a table

Question

Remove duplicate records from a table

Navigation

#1 by (1 votes)

0

Having the following tables without keys yet defined:

Airplane (code_auto, company_code, year, motor_type, type, state)

Flight (FlightCode, FlightNumber, flightCode, CompanyCode)

In some cases, about 400 records from the airplane table, an airplane is associated with two airlines and I do not intend to do so, I intend that each airplane has only one airline. In the following scenario:

Flight code | Company code

N107AS | AS

N107AS | EV

The same plane is associated with two different airlines. It turns out that in the Flight table, when it comes to the N107AS aircraft there are 8000 records for when it is associated with company AS and 1000 for company EV.

What I want to do is remove companies where there are fewer registrations so that each plane has only one airline and the one with the most records in the flight table.

This is for the 400 cases. Is there any automated way to do this or do I have to delete them individually?

sql query

asked by anonymous 17.09.2015 / 13:44

1 answer

Set Default Value HTML Form with PHP Namespace declaration statement has to be the first statement or after any declare call in the script

score 1 · Answer 1

First you will need a query to return the number of flights of an airplane, grouped by company:

select 
    v.cod_companhia t_comp, 
    count(v.cod_voo) as t_voo,
    v.cod_aviao as t_avi
  from 
    voo v
  where
    v.cod_aviao = 'N107AS'
  group by
    v.cod_companhia;

The result of this query will be the number of flights of a certain airplane, grouped by company:

Havingthisresult,wewillincludethisquerywithinanewquery,whichwillreturnthecompanythathastheleastflighttothisairplane,giventhetotalnumberofflights:

selectt_comp,t_avi,min(t_voo)from(selectv.cod_companhiat_comp,count(v.cod_voo)ast_voo,v.cod_aviaoast_avifromvoovwherev.cod_aviao='N107AS'groupbyv.cod_companhia)g_voo;

With the result of this query, it is already possible to determine which airline has the least number of flights with the particular airplane. From this, you can implement a delete statement based on this query.

It's not the solution , but it can help get us one.