using UPDATE with GROUP By

[Ivan Smahin]

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