using UPDATE with GROUP By

[Bart Pietercil]

Hi Ivan

this is interesting. So you can do

>> select
>>    GROUP_CONCAT(RecID),
>>    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 = 2



without having to write in the group by part the recid ? How come ? It was my understanding that any field used in the select had to be in the group by part ?

tia



Bart Pietercil
CTO Cognosis NV



On 21 Jun 2010, at 18:35, william humphrey wrote:

> Yes that is exactly one of the groups I wish to update.
> 
> On Mon, Jun 21, 2010 at 11:28 AM, Ivan Smahin
> <ivan_smahin at paradigmasoft.com> wrote:
>> Hello william,
>> 
>> Sunday, June 20, 2010, 7:35:23 PM, you wrote:
>> 
>>> I'm trying to assign a number to the d_address_linenumber field that
>>> is 1, 2 ,3 or 4 arbitrarily in sequence for each d_address_client
>>> reference number. Most of the records have just one address for a
>>> client reference number and so the d_address_linenumber for those
>>> majority of records would be one. But some of the client reference
>>> numbers have two addresses and a few have three or four.
>> 
>>> It's odd that you don't see the data in that zipped file.
>> 
>>> On Sun, Jun 20, 2010 at 11:54 AM, Bart Pietercil
>>> <bart.pietercil at cognosis.be> wrote:
>>>> Hm,
>>>> 
>>>> suspecting a flaw in your logic; can you describe me what you want to do (limited to the tables we're looking at).
>>>> I have to leave now but I'll try to look at the db and your explanation this evening late or maybe tomorrow
>>>> 
>>>> 
>>>> Bart Pietercil
>>>> CTO Cognosis NV
>>>> 
>>>> 
>>>> 
>>>> On 20 Jun 2010, at 17:22, william humphrey wrote:
>>>> 
>>>>> Same thing with this:
>>>>> 
>>>>> UPDATE data_address SET d_address_LineNumber = 1 WHERE
>>>>> d_address_LineNumber IN (select d_address_LineNumber from
>>>>> (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)
>>>>> )
>>>>> 
>>>>> All 8150 rows are affected an set to 1. GEEZ!
>> 
>> Following this query it is correct.
>> 
>> Look - your subquery alone returns some set of records BUT
>> d_address_LineNumber for each that record is 0.
>> 
>> So  it  sounds  like  "update  ...  where d_address_LineNumber IN (0,0,..,0)
>> And  you  get  all records filled with 2 because almost all records in
>> the table filled with d_address_LineNumber = 0.
>> 
>> -
>> Instead you should get a set of records identified by something unique
>> - say recID.
>> 
>> But  you use grouping ... Well in each group might be few recIds and I
>> don't  get  your  point - what the records you want to update?
>> 
>> 
>> 
>> select
>>    GROUP_CONCAT(RecID),
>>    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 = 2
>> 
>> 
>> Do you want update that list of recIDs???
>> 
>> 
>> 
>> --
>> Best regards,
>> Ivan Smahin
>> Senior Software Engineer
>> Paradigma Software, Inc
>> Valentina - The Ultra-Fast Database
>> http://www.valentina-db.com
>> 
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>> 
> 
> 
> 
> -- 
> http://www.bluewatermaritime.com
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina