Hi all, sorry if this is the wrong place to put this.

I have two tables, both contain address info. I would like to update
address1, address2, city, state, zipcode and country. May be a few
other fields.

The table I am comparing to has many duplicates. The linkage between
the two table is by ssn. However I have one field that is date stamped
as to which is the most current.

How do I get the last date stamped record and update the other table?

update group1 set
address1 = c.address1
address2 = c.address2
city = c.city
state = c.state
zipcode = c.zipcode
country = c.country
from main c, group1 g
where g.ssn = c.ssn and max(lastchanged)

I know the above does not work but it is what I am try to do. Can
anyone help?

TIA!!!!

Assuming the <lastchanged> column belongs to table <main>, and there are no
duplicate values for <lastchanged> per <ssn>, then the following update
should do it:

UPDATE group1
SET address1 = c.address1,
address2 = c.address2,
city = c.city,
state = c.state,
zipcode = c.zipcode,
country = c.country
FROM group1 AS g
JOIN main AS c
ON g.ssn = c.ssn
WHERE c.lastchanged = (SELECT MAX(c1.lastchanged)
FROM main AS c1
WHERE c1.ssn = c.ssn)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Nov 25, 10:27 pm, "Plamen Ratchev" <Pla... [at] SQLStudio.com> wrote:
> Assuming the <lastchanged> column belongs to table <main>, and there are no
> duplicate values for <lastchanged> per <ssn>, then the following update
> should do it:
>
> UPDATE group1
> SET address1 = c.address1,
> address2 = c.address2,
> city = c.city,
> state = c.state,
> zipcode = c.zipcode,
> country = c.country
> FROM group1 AS g
> JOIN main AS c
> ON g.ssn = c.ssn
> WHERE c.lastchanged = (SELECT MAX(c1.lastchanged)
> FROM main AS c1
> WHERE c1.ssn = c.ssn)
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thank You! Worked like a charm.