0

I am using this statement to insert records from another table:

SQL> insert into city(city_code, country_code, city)
select distinct(city_code), country_code, city_name
from airport_final;

  2    3  
9012 rows created.

I found lot of duplicate records:

SQL> SELECT city_code, COUNT(city_code)
FROM airport_final
GROUP BY city_code
HAVING COUNT(city_code) > 1;

VRA        2
WBU        3
YMQ        4
YPR        2

330 rows selected.

I don't know I am selecting distinct records but why its inserting duplicates

Best regards

1 Answer 1

0

DISTINCT operates on the entire row (all columns listed), not one column, so it cannot be used to get uniqueness on just one column.

You need to GROUP BY the key you intend to be unique, and use an aggregation function of some kind on the other attributes:

insert into city(city_code, country_code, city)
select city_code, 
       MAX(country_code), 
       MAX(city_name)
from airport_final
group by city_code

Something similar can be done with windowing functions like ROW_NUMBER which all you to ensure all non-key attributes are taken from the same row, following whichever priority logic you wish to implement in the ORDER BY clause:

insert into city(city_code, country_code, city)
select city_code,
       country_code,
       city_name
  from (select city_code, 
               country_code, 
               city_name,
               ROW_NUMBER() OVER (PARTITION BY city_code ORDER BY city_name) seq
        from airport_final)
  where seq = 1

In reality you probably have dirty data with disagreements about what city name and/or country each city code represents. You'll be faced with the problem of deciding which association is correct and which not. In the above example I just took the lowest alphabetically ranked city name, but the appropriate logic may be quite different. That'll be up to you and your data. But you have to do something to choose between the different associations if you want a table keyed by city code. You may find it impossible to do with code, and instead have to pop it into Excel and manually remove the bad entries, cleaning it using human intelligence. While this is unfortunate, it's a lesson learned in what happens when normalization rules are not followed.

1
  • thanks from the bottom of my heart
    – Raakh
    Commented Dec 11, 2023 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.