using UPDATE with GROUP By

[william humphrey]

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