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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org