using UPDATE with GROUP By

[william humphrey]

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!
>> _______________________________________________
>> 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