Postgres Pro
Downloads
Home   >   mailing lists

Thread: UPDATE Syntax Check

UPDATE Syntax Check

From
Rich Shepard
Date:
   One table, waterchem, with primary key 'site' has columns easting and
northing with no values in them. A second table, sites, with primary key
'name' has values for easting and northing for each row. I want to update
waterchem with the easting and northing values from sites. My proposed
statement is,

   UPDATE waterchem
   SET waterchem.easting, waterchem.northing = sites.easting, sites.northing
   FROM sites AS s
   WHERE waterchem.site = s.name;

   Is this correct? If not, what approach should I adopt?

Rich

Re: UPDATE Syntax Check

From
Thomas Kellerer
Date:
Rich Shepard wrote on 13.06.2012 00:17:
>    One table, waterchem, with primary key 'site' has columns easting and
> northing with no values in them. A second table, sites, with primary key
> 'name' has values for easting and northing for each row. I want to update
> waterchem with the easting and northing values from sites. My proposed
> statement is,
>
>    UPDATE waterchem
>    SET waterchem.easting, waterchem.northing = sites.easting, sites.northing
>    FROM sites AS s
>    WHERE waterchem.site = s.name;
>
>    Is this correct? If not, what approach should I adopt?
>

No it's not quite correct:

UPDATE waterchem
   SET waterchem.easting = s.easting
       waterchem.northing = s.northing
FROM sites AS s
WHERE waterchem.site = s.name;

Re: UPDATE Syntax Check

From
Rich Shepard
Date:
On Wed, 13 Jun 2012, Thomas Kellerer wrote:

> No it's not quite correct:
>
> UPDATE waterchem
>  SET waterchem.easting = s.easting
>      waterchem.northing = s.northing
> FROM sites AS s
> WHERE waterchem.site = s.name;

Thomas,

   I wondered about this syntax, and doing each column separately.

Thanks for the correction,

Rich

Re: UPDATE Syntax Check

From
Rich Shepard
Date:
On Wed, 13 Jun 2012, Thomas Kellerer wrote:

> UPDATE waterchem
>  SET waterchem.easting = s.easting
>      waterchem.northing = s.northing
> FROM sites AS s
> WHERE waterchem.site = s.name;

Thomas,

   For the record, running this pushed me to the correct syntax:

   UPDATE waterchem
     SET easting = s.easting
         northing = s.northing
   FROM sites AS s
   WHERE waterchem.site = s.name;

   The target does not want the table name repeated; postgres looks for a
column named 'waterchem.easting'.

Regards,

Rich

Re: UPDATE Syntax Check

From
David Johnston
Date:
On Jun 12, 2012, at 18:50, Rich Shepard <rshepard@appl-ecosys.com> wrote:

> On Wed, 13 Jun 2012, Thomas Kellerer wrote:
>
>> UPDATE waterchem
>> SET waterchem.easting = s.easting
>>     waterchem.northing = s.northing
>> FROM sites AS s
>> WHERE waterchem.site = s.name;
>
> Thomas,
>
>  For the record, running this pushed me to the correct syntax:
>
>  UPDATE waterchem
>    SET easting = s.easting
>        northing = s.northing
>  FROM sites AS s
>  WHERE waterchem.site = s.name;
>
>  The target does not want the table name repeated; postgres looks for a
> column named 'waterchem.easting'.
>
> Regards,
>
> Rich
>

You are right regarding the table name in the set portion, it has to be the specified table.  However, you can alias
thetable so that instead of waterchem.site you could do something like wc.site 
And, to be thorough, you need to put commas between each field you want to update.

easting = s.easting, northing = s.northing

Re: UPDATE Syntax Check

From
Rich Shepard
Date:
On Tue, 12 Jun 2012, David Johnston wrote:

> And, to be thorough, you need to put commas between each field you want to
> update.

   Did that. Didn't write it that way in the message.

Regards,

Rich