Re: [GENERAL] Update field to a column from another table

[drum.lu...@gmail.com]

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


Lucas

Re: [GENERAL] Update field to a column from another table

[Adrian Klaver]

On 04/21/2016 11:52 AM, drum.lu...@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


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.kla...@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

Re: [GENERAL] Update field to a column from another table

[David G. Johnston]

On Thursday, April 21, 2016, drum.lu...@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.

Re: [GENERAL] Update field to a column from another table

[drum.lu...@gmail.com]

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

Re: [GENERAL] Update field to a column from another table

[David G. Johnston]

Please don't top-post.


> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] On Behalf Of
> > drum.lu...@gmail.com
> > Sent: Donnerstag, 21. April 2016 07:10
> > To: Postgres General 
> > 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 <
> clavadetsc...@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.
​

Re: [GENERAL] Update field to a column from another table

[Charles Clavadetscher]

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-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of
> drum.lu...@gmail.com
> Sent: Donnerstag, 21. April 2016 07:10
> To: Postgres General 
> 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?
> 
> 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