using UPDATE with GROUP By

[william humphrey]

Hi

Please help me with some basic SQL

I have this select statement using group by:

select d_address_LineNumber, d_address_clients_ref, count
(d_address_clients_ref)
from data_address
group by d_address_LineNumber,d_address_clients_ref
having ( count (d_address_clients_ref) > 1)

It works fine

Now what I want to do is update every occurrence of
d_address_LineNumber which comes out in the above but I don't now how
to combine an update with a group by sql (only know how to do it with
a "WHERE")

Something like this (which is nonsense of course):

update data_address
set d_address_LineNumber = 2
FROM (select d_address_LineNumber, d_address_clients_ref, count
(d_address_clients_ref)
group by d_address_LineNumber,d_address_clients_ref) AS duplicateRows
where data_address.d_address_clients_ref = duplicateRows.d_address_clients_ref

Can someone please write me some UPDATE SQL that works with my
AGGREGATE function? I'm doing it because I added a field that will
number duplicate occurrences of the field d_address_clients_ref.

Thanks!

Bill