Error in SQL Developer when trying to update table

Question

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?

Gordon Linoff · Accepted Answer · 2021-06-20 19:01:53Z

4

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');

answered Jun 20, 2021 at 19:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error in SQL Developer when trying to update table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related