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(at)microbizz(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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