SQL Update on selected rows from a lookup table

I have tables structure like following:

Table1: BaseObject (ID, Name)

Table2: Instance (BaseObjectID, Type)

Table3: Mapping (Type, Value)

I want to

Update the BaseObject.Name = Mapping.Value 
where exists an Instance for the 
BaseObject (Instance.BaseObjectID = BaseObject.ID)
 and Mapping.Type = Instance.Type

Not able to figure out how to achieve this in SQL.

Following works but i am not sure if it is optimal:

UPDATE BaseObject 
SET BaseObject.Name = (SELECT Mapping.Value 
                        FROM Mapping, Instance
                        WHERE Mapping.Type = Instance.Type
                        AND Instance.BaseObjectId = BaseObject.ID)
              FROM Instance 
              WHERE Instance.BaseObjectID = BaseObject.ID)

Source: oracle

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.