Update Table Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xRoachx - 13 Oct 2004 17:09 GMT

I recently took over a database that I am in the process of normalizing. I
would like to update a foreign key in one table based on the primary key of
another. The tables and fields are:

tblTraineeInformation tblTraineeList
strName strName
TraID (FK) TraID (PK)

What I would like to do is delete the strName field in tblTraineeInformation
and update the TraID (FK) field with the corresponding TraID (PK) in
tblTraineeList. Can this be done with a query, or is it better to update
through VBA? I have about 20k records to update in tblTraineeInformation
with about 120 records in tblTraineeList.

If I update via VBA, my idea was to pass an array, containing TraID (PK) and
strName (tblTraineeList), to a function, compare strName
(tblTraineeInformation) and strName (tblTraineeList), and update TraID (FK)
if there is a match. Would this be the best way to do it or is there a more
efficient way? Thanks.

Reply to this Message

solex - 13 Oct 2004 17:28 GMT

If you want to perform you tasks with a query I persume that some how the
tables can be connection. If for instance you want to connect the tables by
"strName" then my suggestion would be to run the following query and then
delete the strName field from tblTraineeInformation.

UPDATE tblTraineeList tl SET tl.TraID = ti.TraID INNER JOIN
tblTraineeInformation ti ON ti.strName = tl.TraineeList

> I recently took over a database that I am in the process of normalizing. I
> would like to update a foreign key in one table based on the primary key of
[quoted text clipped - 15 lines]
> if there is a match. Would this be the best way to do it or is there a more
> efficient way? Thanks.

Reply to this Message

Tim Ferguson - 13 Oct 2004 17:44 GMT

> tblTraineeInformation tblTraineeList
> strName strName
> TraID (FK) TraID (PK)

Maybe I am being picky, but I think you would get a much better handle on
what you are trying to do by choosing better table names. It's a good
idea to use names that describe _what_ the table represents, rather than
the job it does. If TraineeInformation is information about trainees,
then I'd call it Trainees. If the TraineeList is a list of trainees, then
I'd call that Trainees. Are they really the same thing? Should they be
one table?

> What I would like to do is delete the strName field in
> tblTraineeInformation and update the TraID (FK) field with the
> corresponding TraID (PK) in tblTraineeList.

That's okay as long as the two name fields are absolutely matchable. You
can't (okay, maybe you can but it would be horrid to do) match
programmatically "Smith, John" with "J.A. Smith III". On the other hand,
to all the "John Smith" records relate to the same John Smith?

> Can this be done with a
> query, or is it better to update through VBA?

Yes: in the query designer join the tables on the names fields and update
the field with the other one. Remember to back up before doing anything,
after the query; then test rigorously before removing the old name field.

Hope that helps

Tim F

Reply to this Message

xRoachx - 13 Oct 2004 18:07 GMT

Thanks for the reply. The table tblTraineeInformation contains other
information related to a training course whereas tblTraineeList only contains
their name and deparment. I only listed the relevant fields. Anyhow, I
tried the update the info based on a query but nothing was updated. My SQL
code is as follows:

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID] WHERE
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name;

The code returns the correct number of records but
[tblCurrentTraineeGrades].[Tra_ID] remains empty. The syntax is correct
because the query ran but I cannot figure out why the data was not updated.
Any ideas?

> > tblTraineeInformation tblTraineeList
> > strName strName
[quoted text clipped - 28 lines]
>
> Tim F

Reply to this Message

solex - 13 Oct 2004 18:53 GMT

The where clause is not needed

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID];

> Thanks for the reply. The table tblTraineeInformation contains other
> information related to a training course whereas tblTraineeList only contains
[quoted text clipped - 43 lines]
> >
> > Tim F

Reply to this Message

xRoachx - 13 Oct 2004 20:23 GMT

I'm still getting the same results. I'm going to try a few other things to
see what I can come up with. Thanks for the help.

> The where clause is not needed
>
[quoted text clipped - 56 lines]
> > >
> > > Tim F

Reply to this Message

solex - 13 Oct 2004 20:53 GMT

Roach,

I did not read you rsql carefully, you are not updating anything, you are
setting the field tblCurrentTraineeGrades.Tra_ID equal to it self, here is
what I believe to be the correct SQL note the last line.

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeList].[Tra_ID];

> I'm still getting the same results. I'm going to try a few other things to
> see what I can come up with. Thanks for the help.
[quoted text clipped - 59 lines]
> > > >
> > > > Tim F

Reply to this Message

xRoachx - 13 Oct 2004 22:27 GMT

Thanks a lot solex, that did the trick. After updating this db for the last
week everything starts to look the same.

> Roach,
>
[quoted text clipped - 85 lines]
> > > > >
> > > > > Tim F

Reply to this Message