December 30, 2015 at 9:22 am
The following UPDATE statement results in an error ( below )
Now, I do have the primary key set to ignore duplicate values.
How do I modify the UPDATE statement to not shout loud but ignore if
we were trying to violate the PRIMARY key.
UPDATE [VISIT_IN]
SET
[CLAIM_ID] = Maxformnbr
,[PROV_NBR] = ProviderMerge
,[FILE_ID] = 95000
from [VISIT_IN] inner join #Temp_Final on formnbr=claim_id and providernbr=[PROV_NBR] and membernbr=mem_nbr and [FILE_ID]=95000
Msg 2627, Level 14, State 1, Procedure sp_Merge_CWP, Line 95
Violation of PRIMARY KEY constraint 'PK__VISIT_IN__38EB8C970A7378A9'. Cannot insert duplicate key in object 'dbo.VISIT_IN'. The duplicate key value is (00010000001, Dec 4 2014 12:00AM, 13993353, 1).
December 30, 2015 at 9:39 am
You can use a NOT EXISTS, but without DDL and sample data, I won't guess the correct statement you need.
You could also convert this into a MERGE statement if you're going to need to delete rows.
December 30, 2015 at 9:47 am
Would you mind passing the SQL syntax please ? I am rather new to the SQL world.. Pardon my ignorance
December 30, 2015 at 9:48 am
How did you set the PK to allow duplicates? The purpose of a PK is to not allow duplicates and the only way I'm aware of to allow duplicates is to remove the PK.
If you try to insert data into the PK column(s) that already exists, it will fail with the PK violation error. If you must have duplicate data in one or more columns, you need to drop the PK or change it to have at least one column where there won't be duplicates.
Example:
PK: Col1, Col2
Col1 Col2 Col3
a 1 apple
You cannot insert 'a', 1, 'banana' as that violates the PK.
But if you change the PK to be Col1, Col2, Col3; you can do the insert as all three columns no longer match.
-SQLBill
December 30, 2015 at 9:51 am
mw112009 (12/30/2015)
Would you mind passing the SQL syntax please ? I am rather new to the SQL world.. Pardon my ignorance
You really need to provide us with full table definition and preferably some sample data for us to properly help you.
Having said that, read this on using EXISTS (https://msdn.microsoft.com/en-us/library/ms188336.aspx). You will need to add NOT to the EXISTS to get the logic you need.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 30, 2015 at 10:01 am
SQLBill (12/30/2015)
How did you set the PK to allow duplicates? The purpose of a PK is to not allow duplicates and the only way I'm aware of to allow duplicates is to remove the PK.If you try to insert data into the PK column(s) that already exists, it will fail with the PK violation error. If you must have duplicate data in one or more columns, you need to drop the PK or change it to have at least one column where there won't be duplicates.
Example:
PK: Col1, Col2
Col1 Col2 Col3
a 1 apple
You cannot insert 'a', 1, 'banana' as that violates the PK.
But if you change the PK to be Col1, Col2, Col3; you can do the insert as all three columns no longer match.
-SQLBill
There's no option to insert duplicates. There's an option to ignore them, but it only works while inserting.
Here's an example:
CREATE TABLE TestDups(
ID int,
CONSTRAINT PK_TestDups PRIMARY KEY (ID) WITH(IGNORE_DUP_KEY = ON)
);
--Insert 4 values
INSERT INTO TestDups
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0)) E(n);
SELECT * FROM TestDups;
--This will ignore duplicate values and insert only missing values
INSERT INTO TestDups
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n);
SELECT * FROM TestDups;
GO
DROP TABLE TestDups
December 30, 2015 at 10:03 am
SQLBill:
I did not set the table to take in duplicates. When you run the following,
your table will no accept duplicates. But at the same time it will not throw errors at you.
So then you are not really violating the primary key.
If you attempt to insert the same key more than once it will just throw
a message "Duplicate key ignored" and go on as if there is no error.
What we are doing is asking SQL sever to "shut up" and move on.
ALTER TABLE [VISIT_IN] REBUILD WITH (IGNORE_DUP_KEY = ON);
December 30, 2015 at 10:06 am
Luis:
The stored proc does not have one insert statement. That is why I was surprised.
The message comes as a result of the "UPDATE statement that I posted. I was surprised the first time and asked.. hmm.. I didn't even do an INSERT ? What is SQL server telling me then ?
December 30, 2015 at 10:16 am
I didn't say your SP had an INSERT statement, I was just explaining Bill how the IGNORE_DUP_KEY option worked. As I mentioned, it only works for INSERT and not for UPDATES.
The problem with your UPDATE statement is that either CLAIM_ID or PROV_NBR (or both) are part of your primary key. To prevent updating to a duplicate key, you should use NOT EXISTS to validate the rows you're updating.
When you need help with the syntax, just select the key word(s) in SSMS and press F1. It'll send you directly to the help for that option.
December 30, 2015 at 10:18 am
Luis and SQLBilly
Here is the fields that make the primary key
MEM_NBR, SERV_DT, CLAIM_ID, CLAIM_LINE_NBR in the VISIT_IN table.
Note:
clustered, ignore duplicate keys, unique, primary key located on PRIMARY
December 30, 2015 at 10:26 am
I think this might work...
I am not quite good at the SYNTAX.. can you help me out
UPDATE [VISIT_IN]
SET
[CLAIM_ID] = Maxformnbr
,[PROV_NBR] = ProviderMerge
,[FILE_ID] = 95000
from [VISIT_IN] inner join #Temp_Final XX on XX.formnbr=claim_id and XX.providernbr=[PROV_NBR] and XX.membernbr=mem_nbr and [FILE_ID]=95000
WHERE NOT EXISTS
(
Select * FROM VISIT_IN
[VISIT_IN]
where
CLAIM_ID = XX.Maxformnbr
and
XX.membernbr=mem_nbr
)
December 30, 2015 at 10:37 am
Maybe something like this. Without sample data, I'm not able to test.
UPDATE v
SET
[CLAIM_ID] = Maxformnbr
,[PROV_NBR] = ProviderMerge
--,[FILE_ID] = 95000 /*This is not necessary*/
FROM [VISIT_IN] v
JOIN #Temp_Final XX on XX.formnbr = v.claim_id
and XX.providernbr = v.[PROV_NBR]
and XX.membernbr = v.mem_nbr
WHERE [FILE_ID]=95000
AND NOT EXISTS
(
SELECT *
FROM VISIT_IN i
WHERE i.CLAIM_ID = XX.Maxformnbr
AND i.MEM_NBR = v.MEM_NBR
AND i.SERV_DT = v.SERV_DT
AND i.CLAIM_LINE_NBR = v.CLAIM_LINE_NBR
);
December 30, 2015 at 10:56 am
LuisC and mw112009,
Thanks for pointing that out. I've never seen or used that before and it will come in handy at work.
It's a good day! I learned something new.
-SQLBill
December 30, 2015 at 11:30 am
Luis:
I fully agree with your syntax but it still gives the same error.
May be some tweaking might help.
December 30, 2015 at 11:42 am
mw112009 (12/30/2015)
Luis:I fully agree with your syntax but it still gives the same error.
May be some tweaking might help.
Without the DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT INTO statements) that is representative of your problem domain, and the expected results based on the sample data it is really difficult to really help you.
Read the first article I have referenced below in my signature block. It will help you understand what we need and how you should post the information.
Also, before posting any code test it in an empty database to be sure everything runs and you can still cause the error you are getting.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply