using UPDATE with GROUP By

[Bart Pietercil]

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