using UPDATE with GROUP By

[william humphrey]

Both

(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)

and

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

and

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)

All give exactly the same result.

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



-- 
http://www.bluewatermaritime.com