Oracle update using multiple tables

Question

Can someone help me with this sql statement to run on Oracle? I am new to Oracle. I tried different ways, but getting syntax error. With this statement I get Missing SET keyworkd error, if I remove employee, I get invalid column name for emp_id.

update emp_bonus, employee                                                               
set emp_bonus.bonus = '555'                        
where emp_bonus.emp_id = employee.emp_id and emp_bonus.bonus_id  = '101';

Fernando A.W. · Accepted Answer · 2014-09-18 18:00:34Z

2

Yes, you have a syntax error. What do you like to do??

In this case the query would be constructed as follows:

UPDATE   EMP_BONUS
   SET   EMP_BONUS.BONUS = '555'
 WHERE   EMP_BONUS.BONUS_ID = '101'
         AND EXISTS (SELECT   'X'
                       FROM   EMPLOYEE
                      WHERE   EMP_BONUS.EMP_ID = EMPLOYEE.EMP_ID);

Where only update the BONUS field when the ID exists in the EMPLOYEE table.

answered Sep 18, 2014 at 18:00

Fernando A.W.

2732 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Fernando A.W. Over a year ago

@user3613050 That's perfect. Anything just ask. Good luck.

user3613050 Over a year ago

Hi Fernando, what should be the command if I need to change the where clause to EMPLOYEE.RATE_ID = '133' instead of a column from other table (EMP_BONUS.BONUS_ID = '101')?

user3613050 Over a year ago

Never mind, adding that to the select statement took care of it.

Fernando A.W. Over a year ago

@user3613050 Only will update more than one time, if any at EMP_BONUS table more than one record with the BONUS_ID = 101

Collectives™ on Stack Overflow

Oracle update using multiple tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related