Hi, Doug! What about:
INSERT into Inventory (ItemCode,Description) SELECT *DISTINCT* ItemCode,Description + FROM Orders + WHERE ItemCode in (SELECT ItemCode from NewItems) (sent from Moto Z) On Fri, Aug 3, 2018, 9:00 PM Doug Hamilton <bugl...@wi.rr.com> wrote: > 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 rbase-l+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- 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 rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
