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&gt; = b.id <http://b.id&gt;
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Please don't top-post.

-----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 =

​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@swisspug.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.
​

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 Thursday, April 21, 2016, drum.lucas@gmail.com <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 = 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....

Providing bits and pieces, without any data, is not going to get us
anywhere.

Create a self-contained test case the exhibits the problem.

David J.

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&gt; = t1.id <http://t1.id&gt;
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The problem was a trigger in my DB, when I disabled it the data started to
be updated.

Lucas