Thread: UPDATE the field of a table with fields from another table

Hi,

I use: Postgres 7.3.10 and Pgadmin 1.4.3 on RH Linux

I got 2 tables with names and telephone numbers:

CREATE TABLE b1_naam
(
   relcode varchar(10),
   naam varchar(100),
   telnr varchar(30)
)

CREATE TABLE b1_telco
(
   relcode varchar(10),
   telnr varchar(30),
   ppc varchar(25)
)

and I need to restore some data. I created something like:

UPDATE b1_naam
   SET telnr = t.telnr
   FROM b1_telco as t
   WHERE
     b1_naam.relcode = b1_telco.relcode AND
     b1_naam.naam like 'Bakke%';

Can anyone tell me what I did wrong? I have seen very complicated
statements with nested selects. I cannot believe that a simple query,
like the one I wrote above, is not available.

Thanks,

Balvie

Balvie <ener@ibb.nl> writes:
> UPDATE b1_naam
>    SET telnr = t.telnr
>    FROM b1_telco as t
>    WHERE
>      b1_naam.relcode = b1_telco.relcode AND
                         ^^^^^^^^ should be t
>      b1_naam.naam like 'Bakke%';

Less-obsolete versions of Postgres would have warned you about this,
or even reported it as an error.

            regards, tom lane

> Balvie <ener@ibb.nl> writes:
>
>> UPDATE b1_naam
>>    SET telnr = t.telnr
>>    FROM b1_telco as t
>>    WHERE
>>      b1_naam.relcode = b1_telco.relcode AND
>>
>                          ^^^^^^^^ should be t
>
>>      b1_naam.naam like 'Bakke%';
>>
>
> Less-obsolete versions of Postgres would have warned you about this,
> or even reported it as an error.
>
>             regards, tom lane
>
>
Thanks Tom,

I did try a lot myself, taking different appraoches and  structures and
different queries. Together with your tip's I changed it finally this
morning to:

update b1_telco
    set  telnr = n.telnr
    from
    b1_telco as t inner join b1_naam as n on t.relcode = n.relcode
    where
        n.naam like 'Bakk%';

and it still has flaws.

Observations:
a. *All* records were affected  instead of those specified in the
where-clause.
b. the value to which the field is set (set  telnr = n.telnr), is the
same for all affected records. My intention was to copy the
corresponding fields.
c. there are no sql errors:  Query returned successfully: 10246 rows
affected, 422 ms execution time.

I'd very much like to hear the response(s).

Balvie