Oracle update with join in execute update jsp

Question

I want to update column1 of table1 by checking the condition with column2 of table1 and column2 of table2.

I just get the error of missing right parenthesis.

I just check the ID of both tables (ID is foreign key to another table) and check the activation code came from query string and if they match I just update the value of status as approve

String s = "Approve";
stmt.executeUpdate("UPDATE  
                                   ( SELECT Approval.STATUS AS st 
                                       FROM Approval 
                                       JOIN Activity 
                                         ON Activity.userid = Approval.id 
                                      WHERE Activity.activationcode = 
                                           '"+activationcode+"') as up SET up.st = '"+s+"'");

Jainik Jainik · Accepted Answer · 2019-02-07T05:18:44

Replace your update table string with below and it should work

UPDATE 
 (SELECT 
    Approval.STATUS AS st 
  FROM 
    Approval JOIN Activity ON Activity.userid = Approval.id 
  WHERE 
    Activity.activationcode = '"+activationcode+"'
 ) up
SET up.st = '"+s+"'

In your query you are trying to update APPROVAL and UP tables both that is why you are getting an error

Oracle update with join in execute update jsp

1 Answers