Search code examples
sqlms-accessduplicatessql-updateorphan

UPDATE query that fixes orphaned records

I have an Access database that has two tables that are related by PK/FK. Unfortunately, the database tables have allowed for duplicate/redundant records and has made the database a bit screwy. I am trying to figure out a SQL statement that will fix the problem.

To better explain the problem and goal, I have created example tables to use as reference: alt text http://img38.imageshack.us/img38/9243/514201074110am.png You'll notice there are two tables, a Student table and a TestScore table where StudentID is the PK/FK.

The Student table contains duplicate records for students John, Sally, Tommy, and Suzy. In other words the John's with StudentID's 1 and 5 are the same person, Sally 2 and 6 are the same person, and so on.

The TestScore table relates test scores with a student.

Ignoring how/why the Student table allowed duplicates, etc - The goal I'm trying to accomplish is to update the TestScore table so that it replaces the StudentID's that have been disabled with the corresponding enabled StudentID. So, all StudentID's = 1 (John) will be updated to 5; all StudentID's = 2 (Sally) will be updated to 6, and so on. Here's the resultant TestScore table that I'm shooting for (Notice there is no longer any reference to the disabled StudentID's 1-4): alt text http://img163.imageshack.us/img163/1954/514201091121am.png Can you think of a query (compatible with MS Access's JET Engine) that can accomplish this goal? Or, maybe, you can offer some tips/perspectives that will point me in the right direction.

Thanks.

Solution

  • The only way to do this is through a series of queries and temporary tables.

    First, I would create the following Make Table query that you would use to create a mapping of the bad StudentID to correct StudentID. 

    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

    Next, you would use that temporary table to update the TestScore table with the correct StudentID. 

    Update TestScore
    Inner Join zzStudentMap
        On zzStudentMap.OldStudentId = TestScore.StudentId
Set StudentId = zzStudentMap.NewStudentId