Hello i have two tables tblPollutants and Pollutant
When i am trying to run the query that exists in the code Snippet: i am getting this error:
Violation of PRIMARY KEY constraint 'PK_tblPollutants'. Cannot insert duplicate key in object 'dbo.tblPollutants'.
the query finds similar values between the two tables and updates the id from the Pollutant table
i have attached an sql script in order to create the two tables
This is the query where i am facing the problemUpdate tblPollutantsset tblPollutants.PollutantID = Pollutant.PollutantIdFROM tblPollutants INNER JOIN Pollutant ON tblPollutants.Pollutant = Pollutant.PollutantName
If those are autonumbered primary key fields, they won't necessarily match between tables and you can't change one to match the other when the other table has a record with that id already existing hence your error message. The main point though is you shouldn't be. If these ids were to match in the first place, the design of the tables/database should be reviewed for possible change and use trigger, cascading keys, or handle inserts in order where you use the SCOPE_IDENTITY() of first insert for subsequent ones.
You are trying to update the primary key column with a value thats already there that's why it is throwing the error
try this on the backup database
Update tblPollutants
set tblPollutants.PollutantID = Pollutant.PollutantId
FROM tblPollutants INNER JOIN
Pollutant ON tblPollutants.Pollutant = Pollutant.PollutantName
WHERE tblPollutants.PollutantID <> Pollutant.PollutantId
Your Pollutant ID 287, 310 are repeated twice and hence you obtain the error.
Fix it out in the INSERT statements to get it corrected.
0
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
aneeshattingal: ti gives me the same error your query
Violation of PRIMARY KEY constraint 'PK_tblPollutants'. Cannot insert duplicate key in object 'dbo.tblPollutants'.
The statement has been terminated.
rrjegan17:
when i am running this query i see this values only once
select pollutantid from pollutant
where pollutantid in (287,310)
mwvisa1: i am joining them on nvarchar column tblPollutants.Pollutant = Pollutant.PollutantName
My point is that those tables primary keys don't have to match. If they are joined by Pollutant, you can just ensure that the value of that field and PollutantName are set as unique in each of tables. To have the numbers match, you would have to review design as stated as you probably don't want to insert these separately as currently doing as each table can assign different auto id based on transactions that occurred before.
rrjegan17: Thanks a lot how did you managed to find that? it seems to working right now let me check it on my main database and i will accept your answer
mwvisa1: Those tables primary keys have to match in my case because i just want to copy the values from table a to table b for for a data warehouseproject
>> Thanks a lot how did you managed to find that? it seems to working right now let me check it on my main database and i will accept your answer
Just tried to find out the error record by running the insert scripts you provided in my machine.
And found what's wrong with the data since syntax is correct.
mwvisa1: yes i could use something like this but i wanted to use the cascade update option if i had delete the values from the tables and insert new values the cascade update option will not work
0
Featured Post
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed