using UPDATE with GROUP By

[william humphrey]

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

On Mon, Jun 21, 2010 at 12:45 PM, Bart Pietercil
It works too as a select but

UPDATE data_address SET d_address_LineNumber = 2 WHERE
d_address_LineNumber IN (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)

If you do the above you get the error:

Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

-- That sure is a nice informative Kernel error. I like how the error
messages in vStudio have been so improved.

Can I use it in
> 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
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>



-- 
http://www.bluewatermaritime.com