Update from multiple tables

0
votes

can you please help me with the following update statement used with join from multiple tables:

  update rol
  set
  rol.role_name = GetCode('ADDRTYPE', ao.Addr_Typ)
  FROM table_addr ao ,
       table_contact con ,
       table_role rol 
  where con.appid = rol.id
  AND ao.use_id = con.con_id
  and rol.role_name='Corporate'

I am getting Oracle error:

SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"

2
oraclesql-update
Have a look at MERGE - Aleksej
@Aleksej - Why? What is the benefit of MERGE over UPDATE in this case? - mathguy
@SocketM - Does the error message give you the line number and position? - mathguy
Yes is on the line (4) FROM table_addr ao , column 3 - SocketM
@mathguy because Oracle doesn't support updates with joins in that format. - Boneist

2 Answers

2
votes

Given that Oracle doesn't support updates from joins like that, a merge statement is probably your best bet.

Something like:

merge into table_role tgt
using (select ao.addr_typ,
              con.appid
       from   table_addr ao
              inner join table_contact con on (ao.use_id = con.con_id)) src
  on (tgt.id = src.appid)
when matched then
  update set tgt.role_name = getcode('ADDRTYPE', src.addr_typ)
  where  role_name = 'Corporate';

This assumes that appid in the src query is unique.

1
votes

Update doesn't support support FROM key word (See Oracle docs). My working solution

is:

      update table_role rol
  set
  role_name = GetCode('ADDRTYPE',
 ( select distinct ao.Addr_Typ 
   FROM table_addr ao ,
        table_contact con 
  where con.appid                  = rol.id
  AND ao.use_id          = con.id
  )

  )
  where
  role_name='Corporate'