INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Duplicate entries problem

SQL Duplicate entries problem

(OP)
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?
 

RE: SQL Duplicate entries problem

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

RE: SQL Duplicate entries problem

(OP)
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?

RE: SQL Duplicate entries problem

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'

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close