Update using a subquery SQL PL/SQL

Question

I am trying to update a table, but I can't get my syntax to work. I added new elements from a temp table to my element table and I want to update column_name in metadata_attribute and table_name in metadata_table table. This is what I have:

UPDATE METADATA_ATTRIBUTE
   SET C.ELEMENT_ID = (SELECT ELEMENT_ID
                         FROM ELEMENT A , TEMP B 
                        WHERE A.ELEMENT_NAME = B.WMIS_COLUMN_NAME)
 FROM ELEMENT A, TEMP B, METADATA_ATTRIBUTE C,  METADATA_TABLE D 
WHERE C.COLUMN_NAME = B.WMIS_COLUMN_NAME
  AND D.TABLE_NAME = B.WMIS_TABLE_NAME
  AND ELEMENT_ID IS NULL;

Answer 1

What happens when you try this?

update a set a.element_id = b.element_id 
from element a, temp b, metadata_attribute c, metadata_table d 
where c.column_name = b.wmis_column_name and 
d.table_name = b.wmis_table_name and 
element_id is null;

Answer 2

Updating two tables from a single update statement is not possible in oracle.

You need to write a PL/SQL block (procedure or trigger) to solve this problem.

Elaborate your question if you need more help.