Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to write correctly this update ?

how to write correctly this update ?

From
Pierre Couderc
Date:
I have tried many ways to update a table  :

UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T2, 
personnes T3
WHERE  ....;


UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T1, 
personnes T2, personnes T3
WHERE ..;


In my case , where clause is : WHERE T1.id=T2.id_mere AND 
T2.id_pere=T3.id AND T1.nom != T3.nom;

Thanks.

PC

Re: how to write correctly this update ?

From
Rob Sargent
Date:
Don’t use the alias on the column(s) being set.
This passed the parser:
UPDATE  personnes T1 SET nom_naiss=T1.nom FROM  personnes T2, personnes T3
WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id AND T1.nom != T3.nom;

(I have no data in my table so I can’t confirm the logic.  You seem to want to update mother’s maiden name if her
current(sur)name doesn’t match that of the father of (one of?) her children?  That might be a serious leap of faith.) 

> On May 22, 2019, at 6:43 AM, Pierre Couderc <pierre@couderc.eu> wrote:
>
> I have tried many ways to update a table  :
>
> UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T2, personnes T3
> WHERE  ....;
>
>
> UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T1, personnes T2, personnes T3
> WHERE ..;
>
>
> In my case , where clause is : WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id AND T1.nom != T3.nom;
>
> Thanks.
>
> PC
>
>
>
>
>
>

Re: how to write correctly this update ?

From
Pierre Couderc
Date:
On 5/22/19 3:19 PM, Rob Sargent wrote:
> Don’t use the alias on the column(s) being set.
> This passed the parser:
> UPDATE  personnes T1 SET nom_naiss=T1.nom FROM  personnes T2, personnes T3
> WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id AND T1.nom != T3.nom;
Wow, fine ! you got  it!
>
> (I have no data in my table so I can’t confirm the logic.  You seem to want to update mother’s maiden name if her
current(sur)name doesn’t match that of the father of (one of?) her children?  That might be a serious leap of faith.)
 
Now I check manually... ;)
>
>> On May 22, 2019, at 6:43 AM, Pierre Couderc <pierre@couderc.eu> wrote:
>>
>> I have tried many ways to update a table  :
>>
>> UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T2, personnes T3
>> WHERE  ....;
>>
>>
>> UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T1, personnes T2, personnes T3
>> WHERE ..;
>>
>>
>> In my case , where clause is : WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id AND T1.nom != T3.nom;
>>
>> Thanks.
>>
>> PC
>>
>>
>>
>>
>>
>>