0

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
3
  • Welcome to Server Fault! Your question is off topic for Serverfault because it appears to relate programming. It may be on topic for StackOverflow but please search their site for similar questions that may already have the answer you're looking for.
    – masegaloeh
    Dec 6, 2014 at 4:17
  • So, what's your question?
    – mustaccio
    Dec 6, 2014 at 15:04
  • 1
    If your syntax is incorrect you will get an error message. What error message did ou get?
    – miracle173
    Dec 6, 2014 at 16:32

2 Answers 2

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy