updating the rows using a group by statement
aft5425Apr 24 2007 — edited Apr 25 2007i have the following sql:
select a.lastname, count(*) from ga_to_sali_leads_wrk a
group by a.lastname, a.address1, a.address2, a.city, a.state
order by a.lastname, a.address1, a.address2, a.city, a.state
table ga_to_sali_leads_wrk has many more fields, two of which are ssn and relation_ssn; what i want to do is...for every record in the same group, update field relation_ssn to what's in the ssn field from the first record in the group;
for example; i have 3 records in the go_to_sali_leads_wrk table
ssn....relation_ssn.....lastname....address1....address2..city...state
123.....000.................wills...........12 main....................rose...ga
333.....000.................wills...........12 main....................rose...ga
465.....000.................wills...........12 main....................rose...ga
because the lastname, address1, address2, city and state are all the same, they are grouped together (i'm assuming they are the same family); i want the relation_ssn updated to contain "123" for all 3 records
Can i do that with an update statement? So far, the update statement hasn't worked.