Error in SQL Developer when trying to update table

user_6415097 0
Viewed 46

I want to update table skuxloc qty to equal the sum of the qty per sku in lotxlocxid as follows:

update skuxloc sl
set sl.qty = lli.sumqty
inner join
(
  select sku, sum(qty) sumqty
  from lotxlocxid
  where loc = 'DRSLN7STG'
  group by sku
) lli on lli.sku = sl.sku
where sl.sku in
('108720-419-S',
'108720-419-XS',
'876070-100-11',
'876070-100-12',
'876070-100-9.5',
'942836-100-10.5',
'942836-100-6.5',
'942837-100-6.5',
'CW5594-100-6',
'CW5594-100-6.5',
'CW5594-100-8',
'DD1583-402-M',
'942836-100-9');

I am getting error at line 2 that this SQL command wasn't properly ended.

Any idea to what I might be doing wrong?

oracle-sqldeveloper oracle sql
1 Answers

Oracle doesn't support join in updates. Instead use a correlated subquery:

update skuxloc sl
    set sl.qty = (select sum(lli.qty)
                  from lotxlocxid lli
                  where loc = 'DRSLN7STG' and
                        lli.sku = sl.sku
                 )
    where sl.sku in
    ('108720-419-S',
    '108720-419-XS',
    '876070-100-11',
    '876070-100-12',
    '876070-100-9.5',
    '942836-100-10.5',
    '942836-100-6.5',
    '942837-100-6.5',
    'CW5594-100-6',
    'CW5594-100-6.5',
    'CW5594-100-8',
    'DD1583-402-M',
    '942836-100-9');
user_11440350
Related