-1

In Oracle, I'm trying to update a a field in one table based off calculations from fields in a second table and I can't seem to get any syntax to work.

For example,

Update item1 i1, item2 i2
Set i1.min_qty = i2.cases * i2.qty_per_case
Where i1.item_id = i2.item_id
And i1.flag1 = 'Y'

The relationship between i1.item_id and i2.item_id is one to one.

Any help would be greatly appreciated.

Nikolay Kostov
  • 16,433
  • 23
  • 85
  • 123
DDS
  • 1
  • 1

1 Answers1

0

You would need to use a correlated subquery or merge statement:

update item1 i1
set    i1.min_qty = (select i2.cases * i2.qty_per_case
                     from   item2 i2
                     where  i1.item_id = i2.item_id)
where  i1.flag1 = 'Y';

merge into item1 tgt
using item2 src
  on (tgt.item_id = src.item_id)
when matched then
update set tgt.min_qty = src.cases * src.qty_per_case
where  tgt.flag1 = 'Y';

N.B. untested.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • Similar question today itself http://stackoverflow.com/questions/28625110/sql-update-in-a-select-rank-over-partition-sentence – Lalit Kumar B Feb 20 '15 at 16:08