Блог

У меня есть таблица Stores и таблица Schools . Это отношение «один ко многим» — одно и то же хранилище может обслуживать несколько школ, но не наоборот.

Ранее при разработке я допустил ошибку, повторив одно и то же хранилище несколько раз в Stores базе данных. Я вставил строки, подобные:

 Store_ID| Store_URL
1       | http://sameurl.com
2       | http://sameurl.com
  

И тогда, если бы две разные школы были в одном и том же магазине, я бы ссылался 1 на одну школьную строку, а 2 на другую.

Я могу довольно легко идентифицировать дубликаты, используя GROUP BY on Store_URL и using COUNT() для идентификации дубликатов.

Трудная задача, стоящая передо мной, заключается в том, чтобы сделать все Schools возможное, чтобы не дублироваться Stores . Если я просто удалю дубликат Stores , у меня будет Schools который указывает на несуществующие строки.

Что я могу сделать, чтобы устранить дубликаты и заставить школы, которые используют одно и то же хранилище, указывать на одну и ту же Store строку?

Примечание: существуют тысячи школ и магазинов. Ручные решения не работают.

Предполагая, что в вашей School таблице есть store_ID из того, что вы сказали.

Я бы начал с определения для каждого дубликата, который store_ID вы хотите сохранить. Я также предположу, что вы хотите, чтобы это было наименьшее значение ID. Затем я бы обновил School s’ store_ID , чтобы он был MIN(store_ID) для текущего URL, который у них есть. После этого вы сможете свободно удалять лишние store_ID записи

Вот как я бы приступил к обновлению:

 UPDATE sch
SET sch.Store_ID = matcher.store_ID
FROM Schools AS sch
INNER JOIN Stores AS st ON sch.store_ID = st.store_ID
INNER JOIN
(
   SELECT MIN(st.store_id) AS store_ID, store_url
   FROM Schools AS sch
   INNER JOIN Stores AS st ON sch.store_ID = st.store_ID
   GROUP BY Store_URL
) AS matcher ON st.Store_URL = matcher.Store_Url
   AND st.Store_ID != matcher.store_ID
  

Если вы можете удалить магазины, у которых нет связанной школы, следующий запрос удалит лишние строки:

 DELETE FROM st
FROM Stores AS st
LEFT JOIN Schools AS sch ON st.Store_ID = sch.Store_Id
WHERE sch.Store_id IS NULL
  

Если вы хотите удалить только дубликаты записей хранилища, я бы рассмотрел этот запрос вместо приведенного выше:

 DELETE FROM st
FROM Stores AS st
INNER JOIN
(
   SELECT MIN(st.store_ID) store_Id, st.Store_Url
   FROM Stores AS st
   GROUP BY st.Store_URL
) AS useful ON st.Store_Url = useful.Store_URL
WHERE st.Store_ID != useful.store_Id
  

Следующая инструкция update изменит значения в базе данных одной школы:

 UPDATE Schools SET store_id = 1 WHERE store_id = 2;
DELETE FROM Stores WHERE Store_ID = 2;