Запрос UPDATE, который исправляет потерянные записи

У меня есть база данных Access, в которой есть две таблицы, связанные PK/FK. К сожалению, таблицы базы данных допускают дублирование/избыточность записей и делают базу данных немного запутанной. Я пытаюсь выяснить оператор SQL, который решит проблему.

Чтобы лучше объяснить проблему и цель, я создал примеры таблиц для использования в качестве справки: http://img38.imageshack.us/img38/9243/514201074110am.png Вы заметите две таблицы: таблицу Student и таблицу TestScore, где StudentID — это PK/FK.

Таблица Student содержит повторяющиеся записи для студентов John, Sally, Tommy и Suzy. Другими словами, Джон со StudentID 1 и 5 — это одно и то же лицо, Салли 2 и 6 — одно и то же лицо и так далее.

Таблица TestScore соотносит результаты тестов с учащимся.

Игнорирование того, как и почему в таблице Student разрешены дубликаты и т. д. Цель, которую я пытаюсь достичь, - обновить таблицу TestScore, чтобы она заменила отключенные идентификаторы StudentID соответствующим включенным StudentID. Итак, все StudentID = 1 (Джон) будут обновлены до 5; все StudentID = 2 (Салли) будут обновлены до 6 и так далее. Вот результирующая таблица TestScore, в которую я стреляю (обратите внимание, что больше нет ссылок на отключенный StudentID 1-4): http://img163.imageshack.us/img163/1954/514201091121am.png Можете ли вы придумать запрос (совместимый с JET Engine MS Access), который может достичь этой цели? ? Или, может быть, вы можете предложить несколько советов/перспектив, которые укажут мне правильное направление.

Спасибо.


sql duplicates sql-update ms-access orphan
person Jed    schedule 14.05.2010    source источник
comment
Вы имели в виду опубликовать структуры таблиц? Как вы идентифицируете дубликаты, это ручной процесс или есть другая таблица, содержащая ТОЛЬКО дубликаты, или вы просто используете студентов с одним и тем же именем и надеетесь, что на самом деле есть не только два человека с одинаковым именем?   -  person brydgesk    schedule 14.05.2010
comment
Да, я разместил структуры в изображении ref. Судя по всему, вы не видите изображения. Вот прямые ссылки на изображения моих примеров структур таблиц: img38.imageshack.us/img38/9243/514201074110am.png img163.imageshack.us/img163/1954/514201091121am.png В моем примере я просто идентифицирую копии по полю Имя. Но в моей реальной базе данных это серийный номер продукта.   -  person Jed    schedule 14.05.2010

Ответы (2)


arrow_upward
1
arrow_downward

Единственный способ сделать это — выполнить серию запросов и временных таблиц.

Во-первых, я бы создал следующий запрос на создание таблицы, который вы использовали бы для создания сопоставления неправильного идентификатора студента с правильным идентификатором студента.

Select S1.StudentId As NewStudentId, S2.StudentId As OldStudentId 
Into zzStudentMap
From Student As S1
    Inner Join Student As S2
        On S2.Name = S1.Name
Where S1.Disabled = False
    And S2.StudentId <> S1.StudentId
    And S2.Disabled = True

Затем вы должны использовать эту временную таблицу для обновления таблицы TestScore с правильным идентификатором студента.

Update TestScore
    Inner Join zzStudentMap
        On zzStudentMap.OldStudentId = TestScore.StudentId
Set StudentId = zzStudentMap.NewStudentId
person Thomas    schedule 14.05.2010
comment
Я не думал использовать временную таблицу. Спасибо, Томас. - person Jed; 14.05.2010

arrow_upward
1
arrow_downward

Наиболее распространенный метод выявления дубликатов в таблице — группировка по полям, представляющим повторяющиеся записи:

ID  FIRST_NAME  LAST_NAME
1   Brian   Smith
3   George  Smith
25  Brian   Smith

В этом случае мы хотим удалить одну из записей Брайана Смита или, в вашем случае, обновить поле идентификатора, чтобы они оба имели значение 25 или 1 (совершенно произвольно, какое из них использовать).

SELECT  min(id)
    FROM example
GROUP BY first_name, last_name

Использование min для ID вернет:

ID  FIRST_NAME  LAST_NAME
1   Brian   Smith
3   George  Smith

Если вы используете max, вы получите

ID  FIRST_NAME  LAST_NAME
25  Brian   Smith
3   George  Smith

Я обычно использую эту технику для удаления дубликатов, а не для их обновления:

DELETE FROM example
      WHERE ID NOT IN (SELECT   MAX (ID)
                           FROM example
                       GROUP BY first_name, last_name)
person Brian    schedule 14.05.2010
comment
Спасибо, Брайан. Это классный метод для удаления дубликатов. Однако, хотя я не против удалить дубликаты из моего образца таблицы Student, я обязательно сохраняю (обновляю) существующие записи в таблице TestScore. Вернувшись к образцу таблицы TestScore, вы заметите, что там есть записи для John(ID=1) и John(ID=5). Проблема в том, что Джон ID1 и ID5 — один и тот же человек. Итак, я хочу обновить все ID=1 до ID=5. Я не хочу терять историю всех тестов Джона (и других студентов). - person Jed; 14.05.2010