Oracle update statement - how do you link to related tables?

Question

I'm relatively new to Oracle, having working on MS SQL for most of my career. I'm used to doing stuff like:

update t
set Col1 = o.Col2
from MyTable t 
join OtherTable o on t.OtherID = o.ID

I tried this syntax in Oracle, and it doesn't accept it. Looked in Oracle docs and couldn't find an example of what I'm trying to do.

How do you do it?

Answer 1

Hello, you might be interested in MERGE statements.

Answer 2

update (select t.OtherID, t.Col1  , o.ID, o.Col2 from MyTable t 
join OtherTable o on t.OtherID = o.ID) a
set a.Col1 = a.Col2

Answer 3

update MyTable t
set Col1 = (SELECT o.Col2 from OtherTable o
            WHERE t.OtherID = o.ID)

Answer 4

Option A) A correlated subquery

UPDATE MyTable t
  SET Col1 = (SELECT o.col2 FROM OtherTable o WHERE t.OtherID = o.ID)

This requires that the subquery return no more than 1 match for each row in the table being updated. If it returns no match, the column will be updated with NULL, which may not be what you want. You could add WHERE EXISTS (SELECT o.col2 FROM OtherTable o WHERE t.OtherID = o.ID) to cause the update to only occur where a match is found.

Option B) Updating a join view

UPDATE (SELECT t.col1, o.col2 FROM MyTable t JOIN OtherTable o ON t.otherID = o.ID)
  SET col1 = col2

This is closer to what you are used to doing. It will only work if Oracle can determine a unique row in the underlying table for each row in the join -- which I think basically means that ID must be a unique key of otherTable.

Answer 5

update MyTable t
set Col1 = (SELECT o.Col2 from OtherTable o
            WHERE t.OtherID = o.ID)
where exists (SELECT o.Col2 from OtherTable o
            WHERE t.OtherID = o.ID)

The "where exists" part is to prevent col1 being set to NULL for MyTable rows that don't have a match in OtherTable - assuming this could happen and needs to be prevented.

Answer 6

You can use a temporary table like so:

update
(
    select t.Col1, o.Col2
    from MyTable t
    join OtherTable o
        on t.OtherID = o.ID
) tmp
set tmp.Col1 = tmp.Col2