Update inner join result Oracle

Question

In my java code I have foreach loop which iterates though list

  foreach(MyObject obj:list){
      String status = obj.getStatus();
      String is = obj.getId();

      // DB call
      1. To update Status in Table A
               jdbcobj.updtastatus(status,id);

       2. Get status from table B

             String tableBStatu= jdbcobj.getstatufromtableB(status,id):
             obj.setStatus(tableBStatus):
    }

To avoid 2 dB calls in for loop I am using inner join and trying to achieve same output as above

I am using inner-join and get the new result set based on common field.I want to update the result set but I am unable to figure out how?

I have two tables "A" and "B".

Table "A" has columns id,name,statusA

Table "B" has columns id,city,statusB

As stated at start, I am using inner-join and my query looks like this.

 Select A.id A.statusA,B.statusB FROM A INNER JOIN ON B where A.id=B.id

 Which gives me result as "id", status from table "A" and status from table "B".

Now i want use the inner-join result, to update statusA column from table "A" and set value ="DONE"

And want to use the statusB column value in java object.

String statusfromColumnB = get statusB col value

and set in my java object like this

      myObj.setStatus(statusfromColumnB)

Sample Data

Suggest a solution.

Have you tried just `UPDATE ( Select A.id A.statusA,B.statusB FROM A INNER JOIN ON B where A.id=B.id ) SET statusA = statusB` ? If certain conditions are met, then something like that will work. — krokodilko, Jan 30 '19 at 17:48

GMB · Accepted Answer · 2019-01-30T19:58:25.017

1

If I understand you correctly, an Oracle MERGE query could properly respond to your need :

Consider :

MERGE INTO A
USING B ON (A.id = B.id)
WHEN MATCHED THEN UPDATE SET A.statusA =  B.statusB

This query will update the status in table A from that of the corresponding record in table B.

Oracle merge is a vendor-specific statement that is optimized for multi-rows upserts (inserts/updates).

Demo on DB Fiddle :

 Select A.id, A.statusA, B.statusB FROM A INNER JOIN B ON A.id=B.id

 ID | STATUSA | STATUSB

 -: | :------ | :--------
  1 | Pending | Initiated
  2 | Pending | Completed

 MERGE INTO A
 USING B ON (A.id = B.id)
 WHEN MATCHED THEN UPDATE SET A.statusA =  B.statusB

2 rows affected

 Select A.id, A.statusA, B.statusB FROM A INNER JOIN B ON A.id=B.id

 ID | STATUSA   | STATUSB

 -: | :-------- | :--------
  1 | Initiated | Initiated
  2 | Completed | Completed

If you want to set statusA to a fixed value instead, then you could go :

MERGE INTO A
USING B ON (A.id = B.id)
WHEN MATCHED THEN UPDATE SET A.statusA =  'Finished'

edited Jan 30 '19 at 19:58

answered Jan 30 '19 at 18:16

GMB

216,147
25
84
135

thanks for suggestion.But I don’t want to make any updates to STATUSB column. I just want to update STATUSA column – hemantmali Jan 30 '19 at 18:53
@hemantmali : this query updates statusA from statusB. It does not update statusB. To which value you want to set statusA ? – GMB Jan 30 '19 at 19:14
I want to set statusA=“Finished” so that all the rows of TableA are updated. And use statusB vaule to set in my java object – hemantmali Jan 30 '19 at 19:54
@hemantmali : I updated my answer with a query to set statusA to "Finished". The Java part looks like a different requirement to me (that was not part of the original post, you may want to ask another question for that, also showing your existing Java code. – GMB Jan 30 '19 at 20:01

score 0 · Answer 2 · answered Jan 30 '19 at 17:48

0

Do you want something like this?

update a
    set (status, somewhereelsecolumn) =
         (select 'DONE', <whatever>
          from b
          where A.id = B.id
         )
    where exists (select 1 from b where a.id = b.id);

answered Jan 30 '19 at 17:48

Gordon Linoff

1,242,037
58
646
786

Update inner join result Oracle

2 Answers2