Problem with updating the database from a temporary table

Question

Problem with updating the database from a temporary table

j.clay 47557

SSC Eights!

Points: 835
More actions
January 8, 2019 at 7:41 am

#379007

Hi there
I'm trying to update a database table from a temporary table that I've created using some SQL script. However, when I try the final bit of the script shown below (the UPDATE part), I get a PRIMARY KEY constraint error. Could anyone please help me resolve this? Each bit of the code is commented out.
My database derives a person's age from their date of birth. I'm using the script below to ascertain their age and then copying this information to a temporary table called TEMPU25:
select distinct contact.serialnumber, contact.dateofbirth, mailingpreference.mailingtype,
GETDATE() AS [todays date], [current age] INTO TEMPU25
from contact
inner join mailingpreference on contact.serialnumber=mailingpreference.serialnumber
CROSS APPLY(VALUES (DATEDIFF(YY,dateofbirth,GETDATE()) - CASE WHEN DATEADD(YY,DATEDIFF(YY,dateofbirth,GETDATE()),dateofbirth) > GETDATE() THEN 1
ELSE 0 END)) A([current age])
where mailingpreference.mailingtype LIKE '%25%' and [current age] >24 and contact.DONOTMAILREASON<>'Deceased'
I'm then making a change to the TEMPU25 table to blank the mailingtype column if it contains a certain value:
UPDATE TEMPU25 set mailingtype='' where mailingtype LIKE '%25%'
I then want to update the live mailingpreference.mailingtype column with the value from tempu25.mailingtype. This is where the issue arises:
UPDATE mailingpreference
SET mailingpreference.mailingtype=''
FROM mailingpreference
INNER JOIN TEMPU25
ON MAILINGPREFERENCE.SERIALNUMBER=TEMPU25.SERIALNUMBER
WHERE MAILINGPREFERENCE.MAILINGTYPE<>TEMPU25.MAILINGTYPE
Any help would be much appreciated, thank you.
Jon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

What is the exact primary key constraint error?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

Is column mailingtype part of the primary key for table mailingpreference?

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 3

Hi both

Thanks for your help.

Jonathan - Yes, mailingtype is a primarykey on the mailingpreferencetable

Michael - the exact error is:
Msg 2627, Level 14, State 1, Line 19
Violation of PRIMARY KEY constraint 'PK_MAILINGPREFERENCE'. Cannot insert duplicate key in object 'dbo.MAILINGPREFERENCE'.
The statement has been terminated.

Best wishes
Jon

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 4

j.clay 47557 - Tuesday, January 8, 2019 7:50 AM
Hi both
Thanks for your help.
Jonathan - Yes, mailingtype is a primarykey on the mailingpreferencetable
Michael - the exact error is:
Msg 2627, Level 14, State 1, Line 19
Violation of PRIMARY KEY constraint 'PK_MAILINGPREFERENCE'. Cannot insert duplicate key in object 'dbo.MAILINGPREFERENCE'.
The statement has been terminated.
Best wishes
Jon

You can only have one value that's '' then.
If you try to update more than one value to '' it will be a duplicate.
Did you actually mean to write the query like this?:
UPDATE mailingpreference SET mailingpreference.mailingtype = TEMPU25.MAILINGTYPE FROM mailingpreference INNER JOIN TEMPU25 ON MAILINGPREFERENCE.SERIALNUMBER=TEMPU25.SERIALNUMBER WHERE MAILINGPREFERENCE.MAILINGTYPE<>TEMPU25.MAILINGTYPE

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 5

Thank you, Jonathan
Yes, I did mean to write that - thanks! First day back after the Christmas break and the brain isn't yet functioning!

Many thanks