From: | "james" <james(at)mercstudio(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #2511: violation of primary key on update with 2 tables |
Date: | 2006-07-03 11:14:37 |
Message-ID: | 200607031114.k63BEbaJ088672@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 2511
Logged by: james
Email address: james(at)mercstudio(dot)com
PostgreSQL version: 8.1.1
Operating system: windows xp professional sp2
Description: violation of primary key on update with 2 tables
Details:
hi,
i've tried to update cust_survey_answer table...
this table links to customer table on
cust_survey_answer.memberno = cust_survey_answer.memberno
cust_survey_answer primary key was
memberno & question_no
this table basically have unique combination of both
memberno & question_no ( answer sheet for customers )
right now, there are confirmed no duplicate of memberno & question_no
combination.
even in customer table, all memberno are uniques...
no duplicate memberno inside customer table.
what i did was, i combined the 2 table, and take the value
customer.dealercode to combine as 1 and store into
cust_survey_answer.memberno...
but when i run the query, the result shows me violation of primary key of
cust_survey_answer...
my temporary solution was, i remove the primary key of the
cust_survey_answer, and ran the script below:
================
update cust_survey_answer set memberno='0'+cast(customer.dealercode as
varchar
(5)) +'-'+ cust_survey_answer.memberno from customer where
cust_survey_answer.memberno=customer.memberno and customer.dealercode is not
null
and length( trim( customer.dealercode ) ) > 0 and
cust_survey_answer.memberno not like '%-%'
and cust_survey_answer.memberno is not null and cust_survey_answer.memberno
<> ''
=============
after i've runs it, i try set back the primary key for table
cust_survey_answer ( combination of memberno & question_no ) , and IT WORKS!
i was surprised..
i think it's a bug in postgresql ...
please help...
thank you.
best regards,
James
From | Date | Subject | |
---|---|---|---|
Next Message | Yann PICHOT | 2006-07-03 15:19:35 | Diffrence between 8.0.3 and 8.1.3 |
Previous Message | Nestor Ramirez (Speedy) | 2006-07-03 11:03:57 | Re: BUG #2507: Problem using two-phase commit |