using UPDATE with GROUP By

[Bart Pietercil]

Hi William,

this is untested !

UPDATE data_address SET d_address_LineNumber = 2 WHERE d_address_LineNumber IN (select d_address_LineNumber from
(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)
))

might be wrong on the ending parenthesis 

hth

Bart Pietercil
CTO Cognosis NV



On 19 Jun 2010, at 23:16, william humphrey wrote:

> 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
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina