using UPDATE with GROUP By

[william humphrey]

exactly as you wrote it I got:

error EOF reached.

If I remove the last parenthesis it crashes vStudio Pro instantly. It
would be a good thing for me to report to Mantis except that I'd have
to upload the database and everything for the bug to repeat.

Bill

PS - Thanks for helping!

On Sun, Jun 20, 2010 at 7:44 AM, Bart Pietercil
<bart.pietercil at cognosis.be> 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
>



-- 
http://www.bluewatermaritime.com