SQL Developer执行update关联更新skuxloc表时报SQL命令未正确结束错误

你使用的是MySQL专属的UPDATE JOIN语法，Oracle数据库不支持这种写法，因此执行时会抛出「SQL命令未正确结束」的语法错误。

提供两种常用实现方式：

方案1：关联子查询实现 #

适合小数据量场景，写法直观

UPDATE skuxloc sl
SET sl.qty = (
    SELECT SUM(qty) sumqty
    FROM lotxlocxid
    WHERE loc = 'DRSLN7STG'
      AND sku = sl.sku
    GROUP BY 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'
)
-- 新增过滤避免无对应数据时qty被更新为NULL
AND EXISTS (
    SELECT 1
    FROM lotxlocxid
    WHERE loc = 'DRSLN7STG'
      AND sku = sl.sku
);

方案2：MERGE语句实现 #

推荐优先使用，性能更优，适合各类数据量场景

MERGE INTO skuxloc sl
USING (
    SELECT sku, SUM(qty) sumqty
    FROM lotxlocxid
    WHERE loc = 'DRSLN7STG'
    GROUP BY sku
) lli
ON (sl.sku = lli.sku)
WHEN MATCHED THEN UPDATE SET sl.qty = lli.sumqty
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'
);

内容的提问来源于stack exchange，提问作者Ravi Shanker Gowda