[SOLVED] Simple "Update" Query problem for pedigree

[forTheDogs]

Greetings to all!!

 

I have a database table [Pedigrees] with a list of RegName and their ID's.  The table also has a column for the dog's Sire and the SireID.  All the Sires are listed  in the RegName and ID columns.

 

ID    RegName    SireID  Sire

1      Rover                  Red

6      Red                      Buddy

 

 

 

I am trying to get the SireID updated by looking up the sires name in the RegName Col then taking the ID and putting it in the SireID field.  In this case, the SireID would be 6.  This is what I tried:

 

UPDATE Pedigrees

SET SireID=ID

Where Sire = RegName.

 

The query does nothing because it is trying to find a record where the Sire and the dog are the same and knows this is not possible!!

 

Is there a way to tell it to look in RegName for the Sire and take that ID?

 

Any help would be greatly appreciated!!!!

 

[Barand]

UPDATE pedigrees a INNER JOIN pedigrees b ON a.sire = b.regname

SET a.SireID = b.ID

 

Caution - I haven't tested so backup table first

 

[forTheDogs]

Thank you so much!!!!  It worked great!!