Please don't top-post.
> -----Original Message-----
> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of
> drum.lucas@xxxxxxxxx
> Sent: Donnerstag, 21. April 2016 07:10
> To: Postgres General <pgsql-general@xxxxxxxxxxxxxx>
> Subject: 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.
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.
