Update Query that updates an ID field if there is no ID

[wwiSports]

I have two tables: "Athlete" and "ExtendedBio". They have a One to One relationship at the field AthleteID. There are over 15,000 athelete's in the database, but there are only 6000+ Extended Bio's--and I need to have an extended Bio for each Athlete (even if it's blank). How can I create an update query that will update all the AhtleteID's in the ExtendedBio table--to the same AthleteID in the Athlete Tabe Where the AthleteID in the ExtendedBio Table Is Null

I tried the following:

UPDATE ExtendedBio
SET AthleteID = Athlete.AthleteID
WHERE ExtendedBio.AthleteID = Is Null;

When I try to run it it asks for a parameter value for "Athlete.AthleteID" and another one for "Is Null"

Beth

 

[Eupher]

I built a couple dummy tables similar to what you described and was able to update the EmployeeID table. At first I experienced the same result as you described. However, I went into the Relationships window and edited the relationship thus: I checked the boxes to Enforce Referential Integrity and Cascade Update Related fields, and I changed the Join type to Left Join. Here's the SQL statement for the update query:

UPDATE Athlete LEFT JOIN ExtendedBio ON Athlete.AthleteID = ExtendedBio.AthleteID SET ExtendedBio.AthleteID = Athlete.AthleteID;

HTH...

Ken S.

 

[wwiSports]

This was perfect! Thanks for taking a moment to figure this one out! Now I will have less trouble exporting the information so we can purge it into Adobe InDesign for our book--any suggestions or preperations that I need for that??

Beth

 

[tlbroadbent]

You can also create an INSERT query to perform this and avoid the extra overhead of updating records that already exist.

Insert Into ExtendedBio (AthleteID)
Select AthleteID From Athlete
Where Not Exists
(Select * From ExtendedBio
Where AthleteID=Athlete.AthleteID) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.