Update Query

[Guest]

I want to update one date field from another date field when the first date
field is null.

Table1 is called BrfDates
SSN Date1
888222123
888333123 3/1/3006
888444564

Table2 is called GtDates
SSN Date2
888222123 1/1/1999
888333123 2/1/1999
888444564 3/1/1999

I would like an update query to return data to the BrfDates table, filling
in the empty Date1 field with the related Date2. Existing BrfDate.Date1
would stay in place.

I have a (see below) query which runs, but doesnt update anything to Date1.
I think it is because I have the same field in the SET line and also the
WHERE line, but I dont know how to do it differently.

Is there a better way to do this update?

UPDATE BrfDates
INNER JOIN GtDates
ON BrfDates.SSN = GtDates.SSN
SET BrfDates.Date1 = GtDates.Date2
WHERE (((BrfDates.Date1) Is Null));

Thanks

 

[Douglas J Steele]

Why do you want to have the same data stored redundantly? It should exist in
one place only.

 

[Guest]

BrfDates.Date1 is a different set of dates than GtDates.Date2, and my users
must ultimately provide a correct Date1 where it is currently blank. But for
this week, I must load this database to an EIS interface as test data and
Date1 is a required field. So, as a stop-gap to get my test data to load, I
am going to default the GtDates.Date to into BrfDates.Date1 where
BrfDates.Date1 is null.

In other words Date1 and Date2 arent really the same, but I need to borrow
some Date2 dates for right now.

Does that make sense?

 

[Douglas J. Steele]

The SQL looks correct.

What happens if you just have

SELECT * FROM BrfDates
INNER JOIN GtDates
ON BrfDates.SSN = GtDates.SSN
WHERE (((BrfDates.Date1) Is Null))

?

If that doesn't return anything, what about

SELECT * FROM BrfDates
INNER JOIN GtDates
ON BrfDates.SSN = GtDates.SSN

 

[Guest]

Thanks for the help-- I think the first one might be what I need.