Update query from another table on datechanged field
am 26.11.2007 03:19:26 von 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!!!!
Re: Update query from another table on datechanged field
am 26.11.2007 05:27:37 von Plamen Ratchev
Assuming the column belongs to table , and there are no
duplicate values for per , 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
Re: Update query from another table on datechanged field
am 26.11.2007 06:16:27 von scoots987
On Nov 25, 10:27 pm, "Plamen Ratchev" wrote:
> Assuming the column belongs to table , and there are no
> duplicate values for per , 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.