Thread: Update field to a column from another table

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.

Here is also an example:

http://www.schmiedewerkstatt.ch/wiki/index.php/PostgreSQL:_Update_rows_with_subquery

Regards
Charles

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> drum.lucas@gmail.com
> Sent: Donnerstag, 21. April 2016 07:10
> To: Postgres General <pgsql-general@postgresql.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 <http://a.id>  = b.id <http://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?
>
> cheers;
> Lucas

So when I run:

UPDATE ja_jobs t2
SET time_job = t1.time_job
FROM junk.ja_test t1
WHERE t2.id = t1.id
AND t2.time_job IS DISTINCT FROM t1.time_job;

I get:

UPDATE 2202

So I check the data by doing:

select * FROM public.ja_jobs WHERE id = 14574527

And the "time_job" field is null....

On 04/21/2016 11:52 AM, drum.lucas@gmail.com wrote:
> So when I run:
>
>     UPDATE ja_jobs t2
>     SET time_job = t1.time_job
>     FROM junk.ja_test t1
>     WHERE t2.id <http://t2.id> = t1.id <http://t1.id>
>     AND t2.time_job IS DISTINCT FROM t1.time_job;
>
>
> I get:
>
>     UPDATE 2202
>
> So I check the data by doing:
>
>     select * FROM public.ja_jobs WHERE id = 14574527
>
>
> And the "time_job" field is null....

First idea:

Are you doing this in two different sessions at the same time, so
something like this?:

     Session 1
     BEGIN;
     UPDATE ja_jobs t2
     SET time_job = t1.time_job
     FROM junk.ja_test t1
     WHERE t2.id = t1.id
     AND t2.time_job IS DISTINCT FROM t1.time_job;

     Session 2
     select * FROM public.ja_jobs WHERE id = 14574527


Where Session 2 is not seeing the UPDATE in Session 1 because the
transaction has not been COMMITed.

Second idea:

Does id = 14574527 meet the criteria AND t2.time_job IS DISTINCT FROM
t1.time_job?

>
>


--
Adrian Klaver
adrian.klaver@aklaver.com