Insert Into & duplicate entries question

[dave3944]

Long time looker, first time poster. This is way over my head. I have spent 3 days looking and trying to solve this problem. Your help is greatly appreciated.
I am trying to populate an AS400 database with 11 fields. I am receiving a "duplicate entries" error. I would expect this, but I need a way to work around it. For instance, a single part number may have up to 5 or 6 different price codes depending on which customer is making the purchase. So, it is necessary to have the part number listed multiple times, but with different pricing codes. Here's what I have been trying so far (remember I'm a SQL newbie). INSERT INTO WEBPRDDT1.WBMSCUR (SPPTNO, SPDESC, SPLDTM, SPCATG, SPPRC1, SPPRC2, SPPRC3, SPPMBQ, SPDATE, SPATA, SPUSER) VALUES ('3408320018','PLASTIC SCREW','035','','0.25','0.23','0.18','0','50812','UAE','DWD') WHERE SPATA='UAE';
If I don't use the "WHERE", I get the "duplicate entries" error. If I DO use the "Where" statement, it tells me that WHERE was not expected. What am I doing wrong?
I want to be able to look at the parts in the database and, if a few key fields are the same (and the pricing code for that part already exists in the database), then update with the changes. If the part number is the same, but the pricing code field does NOT exist, I want it to insert a new entry, and copy all the matching information, with the new pricing code. How can I do this?

 

[markros]

What is your SQL Server version (assuming you're using SQL Server)?
In SQL Server 2008 and up you need to use MERGE statement similar to this

MERGE WebPrddT1.WBMSCur as target
USING (SELECT '3408320018' as SpptNo, 'PLASTIC SCREW' as SPDESC, etc) as source ON target.SPPTNo = source.SPPTNo and target.SPATA = source.SPATA
WHEN MATCHED THEN
  UPDATE
   SET SPDesc = source.SPDESC ...
WHEN NOT MATCHED THEN
  INSERT (...)
  values (source.SPPTNO, source.SPDESC, ...)

Please check the exact syntax in BOL as I wrote from the top of my head.


PluralSight Learning Library

 

[dave3944]

I apologize for my ignorance, I am a newbie. I am using DB2 SQL going to an AS400 server database.

 

[markros]

In this case I suggest to look for the correct forum for your question. This is SQL Server forum and you need a DB2 forum.

PluralSight Learning Library

 

[Qik3Coder]

dave3944,
While this is not a DB2 forum, the basic SQL rules apply.
You probably want something along the lines of:

INSERT INTO (...)
SELECT (...)
FROM WEBPRDDT1.WBMSCUR
WHERE (...)

Keep in mind you will want to INSERT WHERE a row doesn't exist.
You'll need to check the table for your particular set of requirements

Lodlaiden

You've got questions and source code. We want both!