Thread: update a table from a temp table

Referring to the syntax for updating a table from a temp table at "Update a field in a table using a value from another table ..." at
http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm
 
Is this the correct syntax for PG for updating a table from a temp table?
 
 

On 17 August 2010 19:09, Charles Holleran <scorpdaddy@hotmail.com> wrote:
> Referring to the syntax for updating a table from a temp table at "Update a
> field in a table using a value from another table ..." at
> http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm
>
> Is this the correct syntax for PG for updating a table from a temp table?
>

No, you need to use a FROM clause if you wish to update based on another table.

Taking the example from that page you linked to:

UPDATE tableone
SET field1 = tabletwo.fieldx
FROM tabletwo
WHERE tableone.commonid = tabletwo.commonid;

Look at http://www.postgresql.org/docs/current/static/sql-update.html
for more information.

Regards
--
Thom Brown
Registered Linux user: #516935

Thanks.


-----Original Message-----
From: Thom Brown <thom@linux.com>
Date: Tue, 17 Aug 2010 18:20:58
To: <scorpdaddy@hotmail.com>
Cc: <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] update a table from a temp table

On 17 August 2010 19:09, Charles Holleran <scorpdaddy@hotmail.com> wrote:
> Referring to the syntax for updating a table from a temp table at "Update a
> field in a table using a value from another table ..." at
> http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm
>
> Is this the correct syntax for PG for updating a table from a temp table?
>

No, you need to use a FROM clause if you wish to update based on another table.

Taking the example from that page you linked to:

UPDATE tableone
SET field1 = tabletwo.fieldx
FROM tabletwo
WHERE tableone.commonid = tabletwo.commonid;

Look at http://www.postgresql.org/docs/current/static/sql-update.html
for more information.

Regards
--
Thom Brown
Registered Linux user: #516935