My SQL brain cell is stumped.
We have a bunch of orders coming in, most of the items are already in
the db, but, naturally, sometimes they are not and I want to add them on
the fly.
Incoming orders might look like:
Order# ItemCode Description
101 AAA Framus
102 BBB Wingnut
103 CCC Laser
103 EEE Blivet
104 DDD Battery
105 CCC Laser
First, I get a single column temp table, NewItem, of the new items by
subtracting Inventory from Orders:
SUBTRACT Inventory FROM Orders FORMING TEMPORARY NewItems USING ItemCode
Results in:
ItemCode
AAA
CCC
CCC
(I know, the SUBTRACT looks backwards, but it works and that's not the
question.)
Then I clean up the duplicates with:
DELETE DUPLICATES FROM NewItems USING Item
OK, two new items to add to Inventory, AAA & CCC.
ItemCode
AAA
CCC
Piece o' cake, he says glibly:
INSERT into Inventory (ItemCode,Description)
SELECT ItemCode,Description +
FROM Orders +
WHERE ItemCode in (SELECT ItemCode from NewItems)
Oops. Item AAA inserts properly and the first occurrence of CCC also
inserts.
However, the second attempted insert of CCC into the Inventory table
results in a constraint error - ItemCode in the Inventory table is a PK,
Unique key.
So what is the SQL I need to limit the INSERT to just one for each
ItemCode in Orders?
I've experimented with LIMIT, and HAVING/GROUP BY but with no luck.
Thanks much,
Doug
(Fri. eve, off to peruse some Joe Celko books for clues :)
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
