Re: UPDATE syntax

[David Greco <David_Greco@xxxxxxxxxxxxxxx>]

Yeah that's good, but there are plenty of columns, was hoping to be able to use (table.*) syntax



-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@xxxxxxxxxx] 
Sent: Friday, November 30, 2012 3:47 AM
To: David Greco; pgsql-general@xxxxxxxxxxxxxx
Subject: RE:  UPDATE syntax

David Greco wrote:
> Need some help with UPDATE syntax. I am attempting to do something
like this:
> 
> WITH default_facility AS (
>                 SELECT facility_id,
>                 inkjetorlabel
>                 FROM engagement_facility_defs
>                 WHERE engagement_facility_def_id = 8
> )
> UPDATE engagement_facilities SET (
>                 facility_id,
>                 inkjetorlabel
> )
> = ( default_facility.* )
> FROM default_facility
> WHERE engagement_facilities.engagement_id =3
> 
> Postgres errors out on the SET() saying "number of columns does not
match number of values".  Also
> tried default_facility.* without the parenthesis but it does not like
that syntax. This example is a
> bit simplified, in reality there are 90 columns in both lists.
> 
> Is this syntax not possible? I have rewritten it to this form which
works, but I rather like the CTE
> syntax instead.

The problem seems to be the "*".
It works fine with CTE otherwise:

CREATE TABLE test (id integer primary key, val text); INSERT INTO test VALUES (1, 'something');

WITH cte AS (SELECT 1 AS c1, 'other' AS c2)
  UPDATE test SET (id, val) = (cte.c1, CTE.c2)
  FROM cte
  WHERE id=1;

Yours,
Laurenz Albe




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