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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@wien.gv.at]
Sent: Friday, November 30, 2012 3:47 AM
To: David Greco; pgsql-general@postgresql.org
Subject: RE: [GENERAL] 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David Greco wrote:
[wants to use CTEs in an UPDATE]

Yeah that's good, but there are plenty of columns, was hoping to be

able to use (table.*) syntax

Is this a problem or do you just want to type
as little as possible?

You have to specify them in the SET clause anyway.

Yours,
Laurenz Albe

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

David Greco wrote:
[wants to use CTEs in an UPDATE]

Yeah that's good, but there are plenty of columns, was hoping to be

able to use (table.*) syntax

Is this a problem or do you just want to type as little as possible?
You have to specify them in the SET clause anyway.

No problem, just looking to be a bit lazy, and avoid the potential for copy-paste errors and such.

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