Re: Tricky SQL update

On Mon, 2008-09-22 at 15:13 +0200, David FOXWELL wrote:

Hi everyone,

I have a PF, as a result of an extraction, like this :

Client ProductType Profession
C1 PT1 Doctor
C1 PT2 Lawyer
C2 PT2 Binman

I want to modify the profession like this :

Client ProductType Profession
C1 PT1 Doctor
C1 PT2 Doctor
C2 PT2

In the example, the profession must come from PT1 only. If the client doesn't have PT1, his profession is erased. If he has another product as well as PT1 the profession of that product is that recorded for PT1.

Is there an SQL instruction to update the profession in this way ? I think it must be possible but doubt that it would be human readable!

Thanks

Here is my suggestion ...

update aa4391 c
set profession =
( select coalesce ( ( select profession
from aa4391
where client = a.client and
product = 'PT1' )
, ' '
) profession
from aa4391 a
where client = c.client
group by a.client
);

I shall leave it to you to decide whether this is readable and to
determine what that might say about your humanity <grin />.

Cheers,
Terry.