using UPDATE with GROUP By

[Bart Pietercil]

ok, saw your mail, glad it worked !

Bart Pietercil
CTO Cognosis NV



On 20 Jun 2010, at 14:33, Bart Pietercil wrote:

> 1) Can I download your db somewhere (zipped, no index file) ?
> 
> 2) what does 
> 
>>>> 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)
>>>> )
> 
> give ? the expected d_address_LineNumber to update ?
> 
> 
> Bart Pietercil
> CTO Cognosis NV
> 
> 
> 
> On 20 Jun 2010, at 14:23, william humphrey wrote:
> 
>> 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
>> _______________________________________________
>> 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