using UPDATE with GROUP By

[william humphrey]

Exact error with parenthesis in place:

Kernel error: 0X60508. Stream EOF reached

On Sun, Jun 20, 2010 at 8:20 AM, william humphrey
<bill at bluewatermaritime.com> wrote:
> 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
>



-- 
http://www.bluewatermaritime.com