Update Query

[Ray Todd]

I am trying to create an update query that would follow the following rules.
I am banging my head against the wall in an effort to write the SQL but don't
seem to wrap my brain around it (keeping in mind that I am not by any means
an expert).

What I am wanting to do is this:

1. Execute the UPDATE query, which will update the address fields in the
address table if the DateAddressUpdated field is null.

2. Update the AddressUpdateDated to the date that the address fields were
updated.

I have created the UPDATE query to do number 1, however, how do I integrate
1 and 2 into one query so that after the UPDATE query has updated the address
fields, it will add the date to the AddressUpdateDate field?

This is the SQL that I have to execute number 1.

UPDATE taDEFENDANT RIGHT JOIN NewAddresses ON taDEFENDANT.PartyNumber =
NewAddresses.PartyID
SET taDEFENDANT.Address1 = [NewAddresses].[NewAddress], taDEFENDANT.Address2
= [NewAddresses].[NewAddress2], taDEFENDANT.City = [NewAddresses].[NewCity],
taDEFENDANT.State = [NewAddresses].[NewState], taDEFENDANT.ZipCode =
[NewAddresses].[NewZip]
WHERE (((taDEFENDANT.DateAddressUpdated) Is Null));


Thanks,

Ray.

 

[John Spencer]

If you want to use Today's date then the following should work for you

UPDATE taDEFENDANT RIGHT JOIN NewAddresses
ON taDEFENDANT.PartyNumber = NewAddresses.PartyID
SET taDEFENDANT.Address1 = [NewAddresses].[NewAddress]
, taDEFENDANT.Address2 = [NewAddresses].[NewAddress2]
, taDEFENDANT.City = [NewAddresses].[NewCity]
, taDEFENDANT.State = [NewAddresses].[NewState]
, taDEFENDANT.ZipCode = [NewAddresses].[NewZip]
, taDEFENDANT.DateAddressUpdated = Date()
WHERE (((taDEFENDANT.DateAddressUpdated) Is Null));

Question 1: Why a RIGHT JOIN? I would think you would want an INNER JOIN.

Standard Warning: Backup your data first, since this can't be undone.