CODEWITHSUNDEEP
sqlplsqloracle10g
GERMAN PLAZAS
GERMAN PLAZAS

Reputation: 11

Update using a subquery SQL PL/SQL

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;

Upvotes: 1

Views: 3099

Answers (3)

redcayuga
redcayuga

Reputation: 1251

try this:


UPDATE METADATA_ATTRIBUTE m
SET m.ELEMENT_ID =    /* changed C to m */
   (SELECT ELEMENT_ID /* this query must not return multiple rows! */
      FROM ELEMENT A , TEMP B  
      WHERE A.ELEMENT_NAME = B.WMIS_COLUMN_NAME
   )
where m.rowid in /* I added this line */ 
   (select C.rowid
      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
       /* the ELEMENT table is not explicitly joined to any table - Carteansan Product!!!*/
    );

This will update rows in the METADATA_ATTRIBUTE table.

Upvotes: 1

Bharat
Bharat

Reputation: 6866

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.

Upvotes: 3

Madhivanan
Madhivanan

Reputation: 13700

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;

Upvotes: 1

Related Questions