Update Duplicate Error, Need assistance to fix couple lines of code

Question

Ive been experimenting with inserting data and updating if there is a dup. Here is my code. All of these fields are keys. Each is unique and if there is a dup, all three fields are consistent. Only one NUMBE for one TYPE for one TrackingNumber and so on.

    string statement = 
   "INSERT INTO SOP10107 VALUES(@SOPNUMBE, @SOPTYPE, @Tracking_Number) 
    ON DUPLICATE KEY 
        UPDATE(@SOPNUMBE = SOPNUMBE + 1, 
               @SOTYPE = SOTYPE + 1, 
               @Tracking_Number = Tracking_Number + 1 )";

Since I am new, Im not sure if this is correct (or anywhere near correct), I think the basic format is right, but the specific grammer and wording I am having some trouble on. The error will come back and say that there is a problem with "ON". It is most likely a format error. Anyone care to help me out or shed some light on the matter? Thank you so much in advance!

Sparky · Accepted Answer · 2011-10-19T16:36:41.570

2

You can use the new MERGE statement in SQL 2008

MERGE INTO SOP10107 B
USING (
  SELECT sopnumbe,sotype,tracking_Number  FROM SOP10107 ) E
ON (B.tracking_number = E.tracking_number)
WHEN MATCHED THEN
  UPDATE SET B.SOPnumbe  = B.Sopnumber +1,
         B.sotype = B.Sotype +1,
         B.tracking_number = B.tracking_number +1
WHEN NOT MATCHED THEN
  INSERT (sopnumbe,sotype,tracking_Number )
  VALUES (@SOPNUMBE, @SOPTYPE, @Tracking_Number);

MERGE Command in SQL

edited Oct 19 '11 at 16:36

answered Oct 19 '11 at 16:28

Sparky

14,967
2
31
45

Hey Sparky, I really appreciate your time helping me out. This is a great help to my project! Thank you so much. Ill let you know in the next day if I have any issues if youre curious! Thanks again! – javasocute Oct 19 '11 at 16:38

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

Can't use ON DUPLICATE KEY with SQL Server 2008. Only with MySql.

Please review here:

Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE

edited May 23 '17 at 12:27

Community

1
1

answered Oct 19 '11 at 16:27

Jon Raynor

3,804
6
29
43

Update Duplicate Error, Need assistance to fix couple lines of code

2 Answers2