Блог

У меня есть целевая таблица t1, подобная этой (parent и parent_country — соответствующий идентификатор) с несколькими тысячами записей:

 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
...
  

и исходная таблица t2, подобная этой, с несколькими тысячами записей:

 loc_text_1    loc_text_2    loc_text_3 

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

Как я могу вставить значения из t2 В t1 и, кроме того (конечно), только те города, штаты и страны, которых еще нет в t1? (идентификатор в t1 является автоматически сохраняемым первичным ключом)

Добавьте УНИКАЛЬНЫЙ КЛЮЧ на (parent, name) для t1 .

 alter table t1 add unique (parent, name);
  

Этот способ вы можете использовать INSERT IGNORE , чтобы избежать вставки дубликатов.

Затем скопируйте сначала страны, затем штаты, а затем города:

 -- 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'
;
  

Демонстрация: https://www.db-fiddle.com/f/hhpkvgFK6opjFmZwYb7QFv/0

Я думаю, вы можете достичь желаемых результатов, выполнив 3 отдельных INSERT этапа: один для стран, затем один для штатов и, наконец, один для городов:

 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')
  

Вывод (для ваших выборочных данных):

 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
  

Демонстрация на dbfiddle