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'm using Excel 2007 (Microsoft Query) with an ODBC connection to a DB2 AS400 server database.
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?
 

Hi Dave,
You need to do this in some sort of programming language or alternatively, in two stages. I don't think you can issue one piece of SQL that will do everything in one hit.

As you are in Excel, and the data you are inserting is hard-coded, I would first run a query using your Excel data to identify those rows that already exist. Once you have determined what rows are there, create an SQL statement to UPDATE those rows. For the others, the INSERT should work fine.

Hope this helps.

Marc

Thanks for the reply. I appreciate your time.
I am using VBA to send the SQL statement to the AS400 database. So, here's what I understand from what you said: Download the database, make the changes, then upload the whole thing back. That sounds simplistic, I know. I don't understand how this would avoid the problem of duplicate entries. But, I have done this before with other databases. Delete the whole database, then repopulate it with the new data included. Is this kind of what you had in mind?

Hi Dave,
That's another approach - delete and re-populate. If you have all of the data and don't care to keep any of it, then delete everything and insert.

But, if you are not replacing ALL of the data, then a selective insert or update is probably best.

If you are using VBA, can you interrogate the SQLCODE from the insert? If you can, then set up two separate pieces of SQL, one for the INSERT and one for the UPDATE. The following pseudo logic should give you an idea:

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')

IF SQLCODE = -803
UPDATE WEBPRDDT1.WBMSCUR
SET SPPTNO = '3408320018'
  , SPDESC = 'PLASTIC SCREW'
etc.
WHERE SPATA='UAE'