Re: 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 
> 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