SQL Bait-and-Switch

[MDW]

OK, Access97.

I've got two tables, one with bad (redundant) data, one
with clean (unique) data. The table with the bad data is
(of course) the one in production, and I'm trying to clean
it up using the clean data.

However, the column I'm most interested in correcting is
the table's Primary Key and this table is involved in
referential integrity-type relationships with about 4
other tables. I don't want to delete those relationships
because that would mean 4 times as many updates to do.

Here's the types of inconcistancies I'm trying to
eliminate:

If the "good" table lists an ID as '12345', the "bad"
table might list it as '12345', and ALSO as '00012345'.
Both of those values might be included in relationships.
What I want to do is somehow update the '00012345' to
be '12345', and then delete one of the two duplicates. The
trick, of course, is to maintain my relationships so that
anywhere the db saw '00012345', the extra zeroes are
eliminated.

What do you think - am I chasing a white whale here?

 

[John Vinson]

If the "good" table lists an ID as '12345', the "bad"
table might list it as '12345', and ALSO as '00012345'.
Both of those values might be included in relationships.
What I want to do is somehow update the '00012345' to
be '12345', and then delete one of the two duplicates. The
trick, of course, is to maintain my relationships so that
anywhere the db saw '00012345', the extra zeroes are
eliminated.

EEeeuwwww...

My concern would be in the related tables. Might there be duplicate
data, some with 12345 and some with 00012345? Might there be
CONFLICTING data? Blindly updating or deleting related records would
be unwise in the extreme...

I don't see any *easy* way to do this. If you decide that the 12345
record is in fact valid, then you could run Update queries to update
all 00012345 records in the related tables to 12345 (since the
relational integrity is ok), *IF* the data in the related tables is in
fact ok and not duplicated. But you would not be able to use Cascade
Updates to do so, since you cannot change the main table's 00012345 to
12345 without getting a key violation.

Good luck.