Re: BUG #3891: Multiple UPDATE doesn't handle UNIQUE constraint correctly

On Mon, Jan 21, 2008 at 08:17:47AM +0000, Adriaan van Os wrote:
>
> The following bug has been logged online:
>
> Bug reference:      3891
> Logged by:          Adriaan van Os
> Email address:      postgres@microbizz.nl
> PostgreSQL version: 8.2.4
> Operating system:   Mac OS X 10.5
> Description:        Multiple UPDATE doesn't handle UNIQUE constraint
> correctly
> Details:
>
> Suppose we have a table A with a UNIQUE column Name of type VARCHAR, a
> PRIMARY KEY record_id of type INT4 and the following records
>
> A
> record_id   Name
> 1                  X
> 2                  Y
>
> Furthermore, we have a temporary table temp_A with a UNIQUE column Name of
> type VARCHAR, a PRIMARY KEY record_id of type INT4 and the following
> records
>
> temp_A
> record_id   Name
> 1                  Y
> 2                  X
>
> Now, we update table A with values from temporary table temp_A doing
> something like
>
> UPDATE "A" SET "Name" = "temp_A"."Name"  FROM "temp_A" WHERE "A".record_id =
> "temp_A".record_id

You can do something like FROM (SELECT * FROM "temp_A" ORDER BY record_id) AS "A"

so it won't collide.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate