Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Merige returns error code when updating

Posted on 2016-09-19
15
Medium Priority
?
76 Views
Last Modified: 2016-10-26
When I run this after issuing a truncate table command it executes fine. However if I run it  a second time it returns error code 2627. How do I track down which record is causing the error? It is the update that fails but why? There is no violation of primary key. The PK is:
ITEMNMBR,PRCLEVEL8,UOFM,TOQTY,FROMQTY

BEGIN TRY
--Insert or update Additional Information text into internet information table
--Synchronize the target table with
--refreshed data from source table
MERGE rbs_ITEMCOMP AS TARGET
USING (SELECT T1.ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,T1.PRCLEVEL as PRCLEVEL1,SELNGUOM,T3.PRCLEVEL as PRCLEVEL8,
UOFM,TOQTY,FROMQTY,UOMPRICE
FROM IV00101 T1
LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMBR) AS SOURCE
ON (TARGET.ITEMNMBR = SOURCE.ITEMNMBR and TARGET.PRCLEVEL8=SOURCE.PRCLEVEL8 and TARGET.UOFM = SOURCE.UOFM and TARGET.TOQTY = SOURCE.TOQTY and TARGET.FROMQTY = SOURCE.FROMQTY)
--When records are matched, update
--the records if there is any change
WHEN MATCHED
THEN
UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITMSHNAM=SOURCE.ITMSHNAM,TARGET.ITMGEDSC=SOURCE.ITMGEDSC,TARGET.ITMCLSCD=SOURCE.ITMCLSCD,TARGET.STNDCOST=SOURCE.STNDCOST,
TARGET.CURRCOST=SOURCE.CURRCOST,TARGET.LISTPRCE=SOURCE.LISTPRCE,TARGET.ITEMSHWT=SOURCE.ITEMSHWT,TARGET.UOMSCHDL=SOURCE.UOMSCHDL,TARGET.PRICEGROUP=SOURCE.PRICEGROUP,
TARGET.PRCLEVEL1=SOURCE.PRCLEVEL1,TARGET.SELNGUOM=SOURCE.SELNGUOM,TARGET.UOMPRICE =SOURCE.UOMPRICE

--When no records are matched, insert the incoming records from source table to target table

WHEN NOT MATCHED BY TARGET THEN
INSERT ([ITEMNMBR],[ITEMDESC],[ITMSHNAM],[ITMGEDSC],[ITMCLSCD],[STNDCOST],[CURRCOST],[LISTPRCE],[ITEMSHWT],[UOMSCHDL],[PRICEGROUP],[PRCLEVEL1],[SELNGUOM],[PRCLEVEL8],[UOFM]
           ,[TOQTY],[FROMQTY],[UOMPRICE])
                VALUES
           (ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,PRCLEVEL1,SELNGUOM,ISNULL(PRCLEVEL8,''),ISNULL(UOFM,''),ISNULL(TOQTY,1),ISNULL(FROMQTY,999999999999.99000),
               ISNULL(UOMPRICE,0.00));

END TRY

BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
0
Comment
Question by:rwheeler23
  • 7
  • 3
  • 2
  • +2
15 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41805407
It would greatly help if you can put the above code in a code block, and indent with a couple of spaces (as the EE editor interprets tabs as 8-10 spaces), to make it much easier to read.  Thanks in advance.
0
 

Author Comment

by:rwheeler23
ID: 41805439
I have put this code into a file. Is this what you mean?
rbs_ITEMCOMP.sql
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41805442
Uhh .. no.  Use the CODE button to paste code into a question.
ask-question-buttons.jpg
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:rwheeler23
ID: 41805535
Sorry about that, I hope this make the code more readable.

BEGIN TRY
 --Insert or update Additional Information text into internet information table
 --Synchronize the target table with
 --refreshed data from source table
 MERGE rbs_ITEMCOMP AS TARGET
 USING (SELECT T1.ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,T1.PRCLEVEL as PRCLEVEL1,SELNGUOM,T3.PRCLEVEL as PRCLEVEL8,
 UOFM,TOQTY,FROMQTY,UOMPRICE
 FROM IV00101 T1
 LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
 LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMBR) AS SOURCE
 ON (TARGET.ITEMNMBR = SOURCE.ITEMNMBR and TARGET.PRCLEVEL8=SOURCE.PRCLEVEL8 and TARGET.UOFM = SOURCE.UOFM and TARGET.TOQTY = SOURCE.TOQTY and TARGET.FROMQTY = SOURCE.FROMQTY)
 --When records are matched, update
 --the records if there is any change
 WHEN MATCHED
 THEN
 UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITMSHNAM=SOURCE.ITMSHNAM,TARGET.ITMGEDSC=SOURCE.ITMGEDSC,TARGET.ITMCLSCD=SOURCE.ITMCLSCD,TARGET.STNDCOST=SOURCE.STNDCOST,
 TARGET.CURRCOST=SOURCE.CURRCOST,TARGET.LISTPRCE=SOURCE.LISTPRCE,TARGET.ITEMSHWT=SOURCE.ITEMSHWT,TARGET.UOMSCHDL=SOURCE.UOMSCHDL,TARGET.PRICEGROUP=SOURCE.PRICEGROUP,
 TARGET.PRCLEVEL1=SOURCE.PRCLEVEL1,TARGET.SELNGUOM=SOURCE.SELNGUOM,TARGET.UOMPRICE =SOURCE.UOMPRICE

 --When no records are matched, insert the incoming records from source table to target table

 WHEN NOT MATCHED BY TARGET THEN
 INSERT ([ITEMNMBR],[ITEMDESC],[ITMSHNAM],[ITMGEDSC],[ITMCLSCD],[STNDCOST],[CURRCOST],[LISTPRCE],[ITEMSHWT],[UOMSCHDL],[PRICEGROUP],[PRCLEVEL1],[SELNGUOM],[PRCLEVEL8],[UOFM]
            ,[TOQTY],[FROMQTY],[UOMPRICE])
                 VALUES
            (ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,PRCLEVEL1,SELNGUOM,ISNULL(PRCLEVEL8,''),ISNULL(UOFM,''),ISNULL(TOQTY,1),ISNULL(FROMQTY,999999999999.99000),
                ISNULL(UOMPRICE,0.00));

 END TRY

 BEGIN CATCH
     SELECT ERROR_NUMBER() AS ErrorNumber;
 END CATCH;
 GO

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 41805723
Could you check if this query is returning duplicates?
select ITEMNMBR,PRCLEVEL8,UOFM,TOQTY,FROMQTY
  from (
SELECT T1.ITEMNMBR, ITEMDESC, ITMSHNAM, ITMGEDSC, ITMCLSCD,  STNDCOST, CURRCOST, LISTPRCE, ITEMSHWT, UOMSCHDL,PRICEGROUP, T1.PRCLEVEL as PRCLEVEL1,SELNGUOM,T3.PRCLEVEL as PRCLEVEL8,
UOFM,TOQTY,FROMQTY,UOMPRICE
FROM IV00101 T1
LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMBR) source
group by ITEMNMBR,PRCLEVEL8,UOFM,TOQTY,FROMQTY
having count(*) > 1

Open in new window

0
 

Author Comment

by:rwheeler23
ID: 41805794
No records are returned. Even if there were records then the update would kick in, correct? I do not see what is casing the duplicate record.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41805804
Just wanted to check other possibilities. Do you have any triggers on the target table? What exactly the error message?
0
 

Author Comment

by:rwheeler23
ID: 41805817
I have found a clue. It is possible that there are no corresponding records in the IV00108 table so values for the primary key fields are NULL. I am trying to figure out how to account for that.
0
 

Author Comment

by:rwheeler23
ID: 41805818
Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK_rbs_ITEMCOMP'. Cannot insert duplicate key in object 'dbo.rbs_ITEMCOMP'. The duplicate key value is (108                            ,            ,          , 1.00000, 999999999999.99000).
The statement has been terminated.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 41805822
You can exclude NULL possible records by adding a filter condition.
BEGIN TRY
 --Insert or update Additional Information text into internet information table
 --Synchronize the target table with
 --refreshed data from source table
 MERGE rbs_ITEMCOMP AS TARGET
 USING (SELECT T1.ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,T1.PRCLEVEL as PRCLEVEL1,SELNGUOM,T3.PRCLEVEL as PRCLEVEL8,
 UOFM,TOQTY,FROMQTY,UOMPRICE
 FROM IV00101 T1
 LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
 LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMBR
 WHERE T1.ITEMNMBR IS NOT NULL) AS SOURCE 
 ON (TARGET.ITEMNMBR = SOURCE.ITEMNMBR and TARGET.PRCLEVEL8=SOURCE.PRCLEVEL8 and TARGET.UOFM = SOURCE.UOFM and TARGET.TOQTY = SOURCE.TOQTY and TARGET.FROMQTY = SOURCE.FROMQTY) 
 --When records are matched, update 
 --the records if there is any change
 WHEN MATCHED 
 THEN 
 UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITMSHNAM=SOURCE.ITMSHNAM,TARGET.ITMGEDSC=SOURCE.ITMGEDSC,TARGET.ITMCLSCD=SOURCE.ITMCLSCD,TARGET.STNDCOST=SOURCE.STNDCOST,
 TARGET.CURRCOST=SOURCE.CURRCOST,TARGET.LISTPRCE=SOURCE.LISTPRCE,TARGET.ITEMSHWT=SOURCE.ITEMSHWT,TARGET.UOMSCHDL=SOURCE.UOMSCHDL,TARGET.PRICEGROUP=SOURCE.PRICEGROUP,
 TARGET.PRCLEVEL1=SOURCE.PRCLEVEL1,TARGET.SELNGUOM=SOURCE.SELNGUOM,TARGET.UOMPRICE =SOURCE.UOMPRICE

 --When no records are matched, insert the incoming records from source table to target table

 WHEN NOT MATCHED BY TARGET THEN 
 INSERT ([ITEMNMBR],[ITEMDESC],[ITMSHNAM],[ITMGEDSC],[ITMCLSCD],[STNDCOST],[CURRCOST],[LISTPRCE],[ITEMSHWT],[UOMSCHDL],[PRICEGROUP],[PRCLEVEL1],[SELNGUOM],[PRCLEVEL8],[UOFM]
            ,[TOQTY],[FROMQTY],[UOMPRICE])
                 VALUES
            (ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,PRCLEVEL1,SELNGUOM,ISNULL(PRCLEVEL8,''),ISNULL(UOFM,''),ISNULL(TOQTY,1),ISNULL(FROMQTY,999999999999.99000),
                ISNULL(UOMPRICE,0.00));

 END TRY

 BEGIN CATCH
     SELECT ERROR_NUMBER() AS ErrorNumber;
 END CATCH;
 GO

Open in new window

0
 

Author Comment

by:rwheeler23
ID: 41805847
T1.ITEMNMBR would never be NULL so I changed it to T3.ITEMNMBR but I still get the same PK violation message.
0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41839912
What is the primary key from rbs_ITEMCOMP table?
0
 

Author Comment

by:rwheeler23
ID: 41840153
ITEMNMBR
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41840165
Check if the following returns records:
SELECT T1.ITEMNMBR, COUNT(1) Repeated
FROM IV00101 T1
	 LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
	 LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMB
GROUP BY T1.ITEMNMBR
WHERE COUNT(1)>1

Open in new window

0
 
LVL 35

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 total points
ID: 41840588
Changed Where to Having for last comment.

SELECT T1.ITEMNMBR, COUNT(1) Repeated
FROM IV00101 T1
	 LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
	 LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMB
GROUP BY T1.ITEMNMBR
HAVING COUNT(1)>1

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question