MySQL Insert data into table and checking for duplicated values

Question

I have a target table t1 like this (parent and parent_country are the corresponding id) with a few thousand entries:

id    parent    parent_country  name         type

1     0         0               USA          country
2     0         0               Canada       country
3     1         1               Alabama      state
4     1         1               California   state
5     4         1               Los Angeles  city
...

and a source table t2 like this wth a few thousand entries:

loc_text_1    loc_text_2    loc_text_3 

Long Beach    California    USA              
Frankfurt     Hessen        Germany              
Los Angeles   California    USA               
...

How can I INSERT the values from t2 INTO t1 and furthermore (of course) only those cities, states, and countries which are not already in t1? (id in t1 is an autoincremented primary key)

Are the state names known to be unique? That would make the queries required to do this somewhat simpler. — Martin
– Martin, Commented Mar 3, 2019 at 12:54
@PaulSpiegel I have a target table t1 ... with a few thousand entries: — Martin
– Martin, Commented Mar 3, 2019 at 12:56
Are there any UNIQUE keys in t1? You better just post the crate statements for both tables. Use SHOW CREATE TABLE <table name> — Paul Spiegel
– Paul Spiegel, Commented Mar 3, 2019 at 13:02
@Martin No, no column (beside id) is unique. There can duplicates like e.g.: Long Beach in California and Long Beach in New York. — Dirkiboy
– Dirkiboy, Commented Mar 3, 2019 at 13:18

Paul Spiegel · Accepted Answer · 2019-03-03 14:55:59Z

1

Add a UNIQUE KEY on (parent, name) for t1.

alter table t1 add unique (parent, name);

This way you can use INSERT IGNORE to avoid inserting duplicates.

Then copy first countries then states and then cities:

-- copy countries
insert ignore into t1(parent, parent_country, name, type)
  select distinct 0, 0, loc_text_3, 'country'
  from t2
;

-- copy states
insert ignore into t1(parent, parent_country, name, type)
  select distinct t1.id, t1.id, t2.loc_text_2, 'state'
  from t2
  join t1 on t1.name = t2.loc_text_3
  where t1.type = 'country'
;

-- copy cities
insert ignore into t1(parent, parent_country, name, type)
  select st.id, st.parent, t2.loc_text_1, 'city'
  from t2
  join t1 st on st.name = t2.loc_text_2
  join t1 co on co.name = t2.loc_text_3 and co.id = st.parent
  where st.type = 'state'
    and co.type = 'country'
;

Demo: https://www.db-fiddle.com/f/hhpkvgFK6opjFmZwYb7QFv/0

answered Mar 3, 2019 at 14:55

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dirkiboy Over a year ago

Worked perfectly for me. Thanks a lot!

Nick · Accepted Answer · 2019-03-03 14:11:56Z

1

I think you can achieve the results you want by performing 3 separate INSERTs, one for the countries, followed by one for the states and finally one for the cities:

INSERT INTO t1 (parent, parent_country, name, type)
SELECT DISTINCT 0, 0, loc_text_3, 'country'
FROM t2
WHERE NOT EXISTS (SELECT name 
                  FROM t1
                  WHERE name = t2.loc_text_3 AND type = 'country')

INSERT INTO t1 (parent, parent_country, name, type)
SELECT DISTINCT t1.id, t1.id, t2.loc_text_2, 'state'
FROM t2
JOIN t1 ON t1.name = t2.loc_text_3 AND t1.type = 'country'
WHERE NOT EXISTS (SELECT name 
                  FROM t1 t1s
                  WHERE parent = t1.id
                    AND name = t2.loc_text_2
                    AND type = 'state')

INSERT INTO t1 (parent, parent_country, name, type)
SELECT DISTINCT t1.id, t1c.id, t2.loc_text_1, 'city'
FROM t2
JOIN t1 ON t1.name = t2.loc_text_2 AND t1.type = 'state'
JOIN t1 t1c ON t1c.name = t2.loc_text_3 AND t1c.type = 'country' AND t1c.id = t1.parent
WHERE NOT EXISTS (SELECT name 
                  FROM t1 t1t
                  WHERE parent = t1.id
                    AND parent_country = t1c.id
                    AND name = t2.loc_text_1
                    AND type = 'city')

Output (for your sample data):

id  parent  parent_country  name            type
1   0       0               USA             country
2   0       0               Canada          country
3   1       1               Alabama         state
4   1       1               California      state
5   4       1               Los Angeles     city
6   0       0               Germany         country
7   6       6               Hessen          state
8   4       1               Long Beach      city
9   7       6               Frankfurt       city

Demo on dbfiddle

edited Mar 3, 2019 at 14:11

answered Mar 3, 2019 at 13:25

Nick

147k23 gold badges67 silver badges106 bronze badges

7 Comments

Paul Spiegel Over a year ago

You need DISTICT everywhere. dbfiddle.uk/…

Nick Over a year ago

@PaulSpiegel thanks for that. I've updated the answer.

Paul Spiegel Over a year ago

Consider that different countries can have cities with the same name. You need more conditions in the NOT EXISTS subqueries.

Nick Over a year ago

@PaulSpiegel that was a lot more complicated than I originally figured. I think I have it now.

Paul Spiegel Over a year ago

That happens to me too. Waking up in the morning with a new idea, thowing away hours of yesterdays work, rewriting it in 10 minutes :-)

|

Collectives™ on Stack Overflow

MySQL Insert data into table and checking for duplicated values

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related