Update on Oracle with join

Question

I have the Following Statement and need to create an update.

Select p.POS_NR, p1.VON_POS_NR 
FROM table1 p. inner join table2 p1 
ON p.Bestell_NR = p1.Vorgangs_NR 
and p.Bestell_pos = p1.Vorgangs_pos 
where NOT (p.POS_NR = p1.Von_Pos_NR)

Now I want to equal p.POS_NR and p1.Von_POS_NR, but I don't know how to create the update

I hope someone can help me

Please post some sample data and desired result.[ask] and [mcve] can be useful to improve your question — Aleksej, Jun 16 '17 at 13:02
@TomNeumann Edit the question with proper query (minimal but complete in logic) so that proper solutions can be given — Techie, Jun 16 '17 at 13:11
Please, have a look at the link I gave you to post a better question. Also, as said, when you have some more useful content, please edit the question accordingly, instead of adding comments. Also, have you tried searching? I guess that a simple search for something like "Oracle update join" will give useful results — Aleksej, Jun 16 '17 at 13:18

score 0 · Answer 1 · answered Jun 16 '17 at 13:34

You can do this using a MERGE statement. I'll assume that TABLE1 has a primary key field named KEY_FIELD - you can substitute as necessary:

MERGE INTO TABLE1 t1
  USING (SELECT p.KEY_FIELD, p.POS_NR, p1.VON_POS_NR 
           FROM TABLE1 p
           INNER JOIN TABLE2 p1 
             ON p.BESTELL_NR = p1.VORGANGS_NR and
                p.BESTELL_POS = p1.VORGANGS_POS 
           WHERE NOT (p.POS_NR = p1.VON_POS_NR) d
    ON (d.KEY_FIELD = t1.KEY_FIELD)
  WHEN MATCHED THEN
    UPDATE
      SET t1.POS_NR = d.VON_POS_NR;

Best of luck.

Thanks mate. I'll try it. But i'll need to merge in table2 but this should work — Tom Neumann, Jun 16 '17 at 13:54

Update on Oracle with join

1 Answers1