Update a value in a table based on a value in another table

Question

Is it possible to update a value in a table based on a value in another table in Oracle?

I am trying to accomplish something like this(I know that this is not a proper UPDATE statement):

  UPDATE table1 
   SET table1.flag = 1 
  FROM table1 t1 
 INNER JOIN table2 t2
    ON t1.emp_id = t2.emp_id 
 INNER JOIN table3 t3
   ON t2.company = t3.company
 WHERE t1.emp_id = '5632'
   AND l2.company = '83CP'
   AND t1.code = 'LIEU'

@Ben This is not a duplicate. In that question they would like to update the field with a value in another table. I would like to update based on a value in another table (as part of the condition) — Zzz, Jun 25 '13 at 22:30
This is an exact duplicate Azzi, all the solutions proposed in the answers will work exactly the same way. It doesn't matter if you're setting a "flag" or using a column to update. I would use the MERGE option, as I find it easier to understand what's going on. — Ben, Jun 25 '13 at 22:32
@Ben The question is different; whether or not the answer is the same. — Zzz, Jun 25 '13 at 22:38
You're arguing over whether updating a column with the value of another column or a constant is the same or not? I'm the only person who's voted to close so far and you can ignore me entirely, however, whatever answer you get will be _identical_ save for it won't be `update set a.column = b.column` but `update set a.column = 1`. If you don't agree that these are identical then that's your perogative; I would, however, hope that you would be able to apply the code to your own query. If you can't you might want to consider updating your answer with the problems you're having. — Ben, Jun 25 '13 at 22:40

score 1 · Accepted Answer · answered Jun 26 '13 at 05:31

I hope you have a primary key in table1 so use it instead of rowid but if you haven't use

update table1
set flag =1
where rowid in ( select t1.rowid
                 FROM table1 t1 
                      INNER JOIN table2 t2 ON t1.emp_id = t2.emp_id 
                 INNER JOIN table3 t3 ON t2.company = t3.company
                 WHERE t1.emp_id = '5632'
                       AND l2.company = '83CP'
                       AND t1.code = 'LIEU'              
               )

Update a value in a table based on a value in another table

1 Answers1