using UPDATE with GROUP By

[Bart Pietercil]

little optimization I think.

Instead of using two times the count function
> (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)

better do this:

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

hth

Bart Pietercil
CTO Cognosis NV



On 20 Jun 2010, at 13:44, Bart Pietercil wrote:

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