Update query from another table on datechanged field

scoots987

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!!!!

Nov 26 '07 #1

Plamen Ratchev

Assuming the <lastchangedcolumn belongs to table <main>, and there are no
duplicate values for <lastchangedper <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

Nov 26 '07 #2

scoots987

On Nov 25, 10:27 pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:

Assuming the <lastchangedcolumn belongs to table <main>, and there are no
duplicate values for <lastchangedper <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.

Nov 26 '07 #3