0

I am trying to insert data into a table in Oracle database. Data already exists, but not all of the data, and I can't just delete the data and reinsert it all. Is there a way to insert data into the table (without knowing what data I am missing). My script is running, but no data is actually inserting (and I do know there is data missing. I intentionally took data out to test its re-insertion.)

Insert into item (item, descr) 
select distinct a.SUBORD, a.SUBORD_DESCR FROM EXIDE.UDT_BOM a, item b 
where b.item = a.subord and not exists 
(select b.item from item b, exide.udt_bom a where a.subord = b.ITEM)
baskinsr
  • 61
  • 2
  • 16

2 Answers2

2

If I follow what you're doing, you can use the merge statement for this:

merge into item i
using (select subord, subord_descr from exide.udt_bom) u
on (i.item = u.subord)
when not matched then insert (item, descr) values (u.subord, u.subord_descr);

SQL fiddle demo.

This also has the advantage that if the udt_bom has new descriptions for existing items, you can update those in the item table too:

merge into item i
using (select subord, subord_descr from exide.udt_bom) u
on (i.item = u.subord)
when matched then update set descr = u.subord_descr
when not matched then insert (item, descr) values (u.subord, u.subord_descr);

Another fiddle.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • When I run the merge statement, I get a unique constraint, primary key violation. So it's still trying to insert items that already exist in the Item table. – baskinsr Sep 18 '13 at 16:31
  • What's your PK, `item`? You have the `matched` and `not matched` the right way round, right? (I think I do!). And all changes in other sessions are committed? Here's a [SQL Fiddle with a PK](http://sqlfiddle.com/#!4/478be/1) to show it works; so I'm not sure what you're doing differently? – Alex Poole Sep 18 '13 at 16:39
  • A new error has arisen that says "ORA30926: Unable to get a stable set of rows into the source tables" – baskinsr Sep 18 '13 at 16:40
  • Yeah. I tested out your SQL Fiddle that you had attached and saw that it works properly. After ensuring all was the same, the new error came up. – baskinsr Sep 18 '13 at 16:41
  • 2
    Your query "select subord, subord_descr from exide.udt_bom" has duplicate rows -- subord is not unique in it. – David Aldridge Sep 18 '13 at 16:41
  • @user2529787 - so you have duplicates in `udt_bom` then I think? [See this](http://stackoverflow.com/q/2337271/266304). If both `subord` and `subord_descr` are the same in multiple rows then I think you can just make the `using` clause `select distinct`... – Alex Poole Sep 18 '13 at 16:42
  • Ok. Thanks. So I need to remove duplicates for it to work properly? – baskinsr Sep 18 '13 at 16:44
  • I have made the change to say select distinct, but am still returning the same "Stable set of rows" error. – baskinsr Sep 18 '13 at 16:48
  • Do you have the same `subord` with different `subord_descr`? If so you'd need to decide which you'd want to use, and get the query inside the `using` clause to get a set of data which only has each `subord` once. (Options include picking the max, or the most recent if there's a date field, or the first by some other criteria - you'll have to judge based on the data). – Alex Poole Sep 18 '13 at 16:52
  • I'm thinking that the subord_descr is causing my issue. When I remove it, the statement works. So it is a data problem on my end. Thank you guys so much for all your help. – baskinsr Sep 18 '13 at 16:55
1

You have too many references to too many tables. With the not exists clause, the query does not need explicit joins:

Insert into item(item, descr) 
    select distinct b.SUBORD, b.SUBORD_DESCR
    FROM EXIDE.UDT_BOM b
    where not exists (select i.item from item i where b.subord = i.ITEM);

If there are no duplicates, in udt_bom, I would get rid of the distinct as well. And, queries are more readable when you use table abbreviations as aliases, rather than meaningless letters like a, b, and so on.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • When I run the merge statement, I get a unique constraint, primary key violation. So it's still trying to insert items that already exist in the Item table. – baskinsr Sep 18 '13 at 16:32
  • I do appreciate the suggestions for replacing the letters with something more readable and understandable. Will be very helpful to me. – baskinsr Sep 18 '13 at 16:33
  • After checking some stuff, I found that some of my data is incorrect with subord_descr, so when I remove it, the script works. Thank you for you help, as well as the suggestions for query cleanup. – baskinsr Sep 18 '13 at 16:57