Thread: UPDATE syntax
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.
UPDATE engagement_facilities SET (
facility_id,
inkjetorlabel
)
= ( df.facility_id, df.inkjetorlabel )
FROM engagement_facility_defs df
WHERE engagement_facility_def_id = 8
AND engagement_facilities.engagement_id =3
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
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
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
>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.