Skip site navigation (1) Skip section navigation (2)

Feature Request: additional extension to UPDATE

From: "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Feature Request: additional extension to UPDATE
Date: 2008-08-28 05:58:03
Message-ID: 396486430808272258l824b796hbdb1d235846796c9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Regarding:  row-wise update.

In a previous thread:
http://archives.postgresql.org/pgsql-sql/2008-08/msg00122.php

it was mentioned that the "UPDATE ... SET  ... FROM ... WHERE" syntax
is an extension. (an extension employed by many other db flavors.)
This creates a problems since each db flavor is left to their own
devices in how to implement it.

However the ANSI SQL syntax can be a real hassle when you have to a
lot of fields to update using (complicated) correlated sub-queries:

UPDATE Foo
   SET a = ( SELECT newA  FROM Bar WHERE foo.id = Bar.id ),
          b = ( SELECT newB  FROM Bar WHERE foo.id = Bar.id ),
          c = ( SELECT newC  FROM Bar WHERE foo.id = Bar.id ),
          ...
          n = ( SELECT newN FROM Bar WHERE foo.id = Bar.id )
WHERE
    Foo.id < 100;


Maybe a row-wise update could simplify this process and at the same
time produce results in with those defined by the SQL Standard.

UPDATE Foo
   SET ( a, b, c, ..., n ) = ( SELECT newA, newB, newC, ..., newN
                                        FROM Bar WHERE foo.id = Bar.id )
WHERE
    Foo.id < 100;


Any thoughts on this?

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Responses

pgsql-general by date

Next:From: Craig RingerDate: 2008-08-28 06:18:28
Subject: Re: Do I have a corrupted database?
Previous:From: Matthew DennisDate: 2008-08-28 05:30:07
Subject: indexes on functions and create or replace function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group