using update query with subquery in Oracle sql

Question

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement but I think my syntax is incorrect below.

UPDATE sku@bn2 x 
INNER JOIN (
   select g.prev_perm_ret, g.itm_cd
   from gm_prc_reg_prc_items g, sku@bn2 b
   where g.itm_cd=b.stylecode
   and b.storecode=00000
   and g.prev_perm_ret<>b.listprice
   and g.prev_perm_ret>g.ret_prc
   and b.sellprice=b.listprice
) y ON x.stylecode=g.itm_cd
SET x.listprice=y.prev_perm_ret

Answer 1

I would recommend to have a look into the merge into statement when youre deailing with oracle.

if you just want to update rows omit when not matched part of the statement

see http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/sql-merge/index.html and https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

Answer 2

I generally use PL/SQL collections for this. Essentially you create a table type, then create a variable with that table type, then do a select for update to get all of the data that you want to update, then do a forall update to update the data. Since you are updating the a table the record type should be that table. Such as:

TYPE sku_tab_type IS TABLE OF sku@bn2%ROWTYPE
    INDEX BY PLS_INTEGER;
sku_table sku_tab_type;

Then do a select for update and a forall update a commit and you are done. If you needed to you could probably load the data with a limit clause in a loop so that you could process a small chunk of data at a time. I would start by trying to update all of the data at once.

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

Here is my best guess on two indexes that could help the performance. You probably need to test with and without these indexes, or maybe you already have indexes that do enough.

CREATE INDEX gm_prc_reg_prc_items_idx1 ON gm_prc_reg_prc_items_idx1 
     ( storecode, listprice, sellprice, stylecode );

CREATE INDEX sku_IDX@bn2 ON sku@bn2
     ( prev_perm_ret, ret_prc, itm_cd );