467,879 Members | 1,301 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

Update query from another table on datechanged field

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
  • viewed: 3237
Share:
2 Replies
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Mike Leahy | last post: by
3 posts views Thread by rrh | last post: by
15 posts views Thread by Darren | last post: by
3 posts views Thread by melissa820 | last post: by
reply views Thread by jack112 | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.