Oracle UPDATE Question

Question

Can the update described below be completed in one UPDATE statement?

I want to update the Operators.name_id values with the Users.name_id values by joining on Users.name = Operators.op_name. Both Users.name and Operators.op_name are have a unique.

I know the situation described below doesn't follow "best practices", but it's a much simpler example of what I'm trying to do: namely updating a field with the value from another joined table.

Table: Users
user_id    name
----------------
34         Billy
43         Jimmy
50         Joe

Table:  Operators (before UPDATE)
op_id   op_name   user_id
-------------------------
12      Billy     35
35      Jimmy     46
33      Joe       99


Table:  Operators (after UPDATE)
op_id   op_name   name_id
-------------------------
12      Billy     34
35      Jimmy     43
33      Joe       50

Answer 1

UPDATE   operators o
SET      user_id = 
         (
         SELECT  u.user_id
         FROM    users u
         WHERE   o.op_name = u.name
         )
WHERE    o.op_name IN
         (
         SELECT  name
         FROM    users
         )

In Oracle 10g, more efficient:

MERGE
INTO    operators o
USING   users u
ON      (u.name = o.op_name)
WHEN MATCHED THEN
UPDATE
SET     user_id = u.user_id

Answer 2

update (
       select oo.user_id, uu.user_id uu_id
         from operators oo
              join
              users uu on uu.name = oo.op_name
       )
   set user_id = uu_id

Here's the full test script (I'm using my_users and my_operators so as not to mess with your data).

drop table my_users;
create table my_users (
                      user_id number(2),
                      name    varchar2(30) unique
                      )
;
insert into my_users (user_id, name)
  select 34, 'Billy' from dual union all
  select 43, 'Jimmy' from dual union all
  select 50, 'Joe'   from dual
;
drop table my_operators;
create table my_operators (
                          op_id   number(2),
                          op_name varchar2(30) unique,
                          user_id number(2)
                          )
;
insert into my_operators (op_id, op_name, user_id)
  select 12, 'Billy', 35 from dual union all
  select 35, 'Jimmy', 46 from dual union all
  select 33, 'Joe',   99 from dual
;
update (
       select oo.user_id, uu.user_id uu_id
         from my_operators oo
              join
              my_users uu on uu.name = oo.op_name
       )
   set user_id = uu_id
;
select * from my_operators;