0

I have table with columns : xx_org

Org_name source_id source_owner

and another table with : master_org

name, source_id,source_owner, class

now i have to update all the source_ids in master_org with source_id in xx_org for whatever org name is there in xx_org and master_org which is common. for uncommon ones nothing should be done.

I used :

    update HR.master_org T1,XX_ORG T2 
set  T1.SOURCE_ID=T2.SOURCE_ID
    WHERE t2.ORG_NAME = t1.name ;

But this is giving an error :

Error report:
SQL Error: ORA-00971: missing SET keyword
00971. 00000 -  "missing SET keyword"
*Cause:    
*Action:

Looking at other posts given i also tried :

UPDATE 
(select TABLE1.SOURCE_ID as old, TABLE2.SOURCE_ID as new
 from XX_ORG TABLE1
 INNER JOIN HR.master_org  table2
 ON table1.ORG_NAME = table2.name
) T
SET t.old = t.new

I got :

Error report:
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

As suggested in Update statement with inner join on Oracle

I tried using merge because other queries didnt work but even after showing 1400 rows merged i cannot see my rows updated. I am using :

merge
INTO    hr.master_org trg
using   (
        SELECT  t1.rowid AS rid, t2.SOURCE_ID,t2.SOURCE_owner
        FROM    hr.master_org t1
        join    XX_ORG T2
        on      T1.ORGANIZATION_NAME = t2.ORG_NAME
        where   T1.ORGANIZATION_NAME = T2.ORG_NAME
       -- and t1.ORGANIZATION_NAME='TMI Operations, M1'
        ) src
ON      (trg.rowid = src.rid)
when matched then update
    set TRG.SOURCE_id = SOURCE_id,
    TRG.SOURCE_owner = SOURCE_owner;

sample data :
XX_ORG
source_id source_owner  org_name
EB73636   EBS           TMI

MASTER_ORG

source_id      source_owner  org_name
EB7363-30-JAN   FUSION         TMI

I want to update master_org in such a way that 

   source_id      source_owner  org_name
    EB73636        EBS            TMI
Community
  • 1
  • 1
divya.trehan573
  • 454
  • 1
  • 12
  • 28
  • 4
    It looks like you are trying to update based on inner joins. Try looking at an answer like this: http://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle – zedfoxus Mar 16 '16 at 18:11
  • @zedfoxus- I did. Pls check my edited post. I still faced an error – divya.trehan573 Mar 16 '16 at 18:17
  • Did you see the "it depends if the inline view is considered updateable by Oracle" part of that solution? That error is telling you that it is not. You need to use the first syntax Tony gave, with subqueries and an `exists` check. – Alex Poole Mar 16 '16 at 18:18
  • Hi @AlexPoole- No merge one answer worked Thanks :) – divya.trehan573 Mar 16 '16 at 18:22
  • @AlexPoole- I did try the merge query it is showing 1400 roows merged but on querying i cannot see the result – divya.trehan573 Mar 16 '16 at 19:09
  • @AlexPoole- I have updated my questions – divya.trehan573 Mar 16 '16 at 19:20
  • Your naming is inconsistent and confusing. But you seem to be setting the master values to themselves. Change the `set` to specify the source table, e.g. `TRG.SOURCE_SYSTEM_ID = SRC.SOURCE_SYSTEM_ID`, or whatever the columns are really called in both. – Alex Poole Mar 16 '16 at 19:28
  • @AlexPoole- I am trying to set source sys id in master table with source sys if of xx_org table – divya.trehan573 Mar 16 '16 at 19:30
  • Yes, but you are currently *effectively* doing `set TRG.SOURCE_SYSTEM_ID = TRG.SOURCE_SYSTEM_ID`. That obviously isn't what you meant. Add the SRC alias to both update columns and see what happens. – Alex Poole Mar 16 '16 at 19:38
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/106503/discussion-between-divya-trehan573-and-alex-poole). – divya.trehan573 Mar 16 '16 at 19:55
  • @AlexPoole- Also my merge will not update the existing table. Now i have two rows with the above statement i want an update – divya.trehan573 Mar 16 '16 at 20:30

0 Answers0