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) WHERE EXISTS (SELECT Instance.BaseObjectID FROM Instance WHERE Instance.BaseObjectID = BaseObject.ID)