Re: How to do an UPDATE for all the fields that do NOT break a constraint?

On Mon, Jan 26, 2009 at 2:09 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> I wonder if this is an SQL limitation or something I'm missing in the
> PG manual, but I need to run an update on my database (to replace the
> value of a column to match a new design structure).
>
> Due to the new business logic, the replaced value of a field may end
> up being already present in the database in another record. This leads
> to unique key violations when I run the update.
>
> My question: I don't mind if the update transaction skips the records
> where the key would be violated (this preservation is in fact what we
> want) but these are only about 2% of the overall updatable records.
>
> Is there anyway to make the transaction go through with the remaining
> 98% of the update SQL which will in fact NOT violate the unique
> constraint?
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

You could always extend your update statement to include an additional
check to see if there are already rows present with the same value in
the field you are talking about.

// Matthias