Search Postgresql Archives

Re: Exists subquery in an update ignores the effects of the update itself

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Jeff Janes <jeff.janes@xxxxxxxxx> wrote:

> I want to update some data in unique column.  Some of the updates
> would conflict if applied to eligible rows, and for now I want to
> skip those updates, applying only one of a set of conflicting
> ones.
>
> create table foo (x text unique);
> insert into foo values ('aac'),('aad'),('aae');
>
> update foo a set x=substr(x,1,2) where x!=substr(x,1,2)
>   and not exists (select 1 from foo b where b.x=substr(a.x,1,2));
>
> ERROR:  duplicate key value violates unique constraint "foo_x_key"
> DETAIL:  Key (x)=(aa) already exists.
>
> Is there a way to phrase this in a single statement so it will do
> what I want, updating one row and leaving two unchanged?

update foo a
  set x = f2.x2
  from (
         select distinct on (substr(x,1,2))
             x, substr(x,1,2) as x2
           from foo
           order by substr(x,1,2), x
       ) f2
  where a.x = f2.x
    and not exists (select * from foo b where b.x = a.x)
;

The exists test is only there to cover any conflicting rows that
may exist before the statement starts; if you know there are none,
it could be omitted.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux