Re: Update field to a column from another table

Please don't top-post.

> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
> > drum(dot)lucas(at)gmail(dot)com
> > Sent: Donnerstag, 21. April 2016 07:10
> > To: Postgres General <pgsql-general(at)postgresql(dot)org>
> > Subject: [GENERAL] Update field to a column from another table
> >
> > I've got two tables:
> >
> > - ja_jobs
> > - junk.ja_jobs_23856
> >
> > I need to update the null column ja_jobs.time_job with the data from the
> table junk.ja_jobs_23856
> >
> > So I'm doing:
> >
> >
> > UPDATE public.ja_jobs AS b
> > SET time_job = a.time_job
> > FROM junk.ja_jobs_23856 AS a
> > WHERE a.id =
> ​b.id​
>
> > AND a.clientid = b.clientid;
> >
> >
> > But it's now working... I'm using PostgreSQL 9.2
> >
> > Do you guys have an idea why?
> >
>

​Define "not working".

The query itself looks fine.

The likely cause is there are no records that share both an "id" and a
"clientid" value.

> ​
> ​
> On Wed, Apr 20, 2016 at 10:53 PM, Charles Clavadetscher <
> clavadetscher(at)swisspug(dot)org> wrote:
>
>> Hi
>>
>> This could work:
>>
>> UPDATE public.ja_jobs
>> SET time_job = a.tj
>> FROM
>> (
>> SELECT id AS rid,
>> clientid AS cid,
>> time_job AS tj
>> FROM junk.ja_jobs_23856
>> ) AS a
>> WHERE a.rid = id
>> AND a.cid = clientid;
>>
>> In the subselect a you need to rename the column names to avoid ambiguity.
>>
>
This shouldn't make any different. The original query prefixed column
names with their source table so no ambiguity was present.

​David J.
​